HLookup, multiple tables and range names in other cells

[Matt Harris]

Hello,

I am beginning to discover Excel with the aid of various textbooks but
I am stumped on one problem and don't wish to embark on VBA custom
functions unless I have to.

I am intending to use HLookup with multiple tables (over 15) in the
same worksheet. So here is my problem,

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

The 'table_array' part can be a range or the name of a range, if I
have a cell that is calculated to produce the name of a range eg. an
INDEX and MATCH formula, how can I get 'table_array' to accept this
without errors?

eg.=HLOOKUP(A12,C5,2,TRUE)

where the cell C5 displays the name of a data range Model_1,
corresponding to a range D1:H2.
The reason i'm trying to do this is that cell C5 will change range
names depending on what data I have put into the spreadsheet.

Any ideas on getting round this problem in any way?

Thanks in advance,

Matt Harris

Leeds, W. Yorkshire
Great Britain

 

[JulieD]

Hi Matt

if i'm understanding correctly try

=HLOOKUP(A12,Indirect(C5),2,TRUE)

Cheers
JulieD

 

[kraljb]

Use INDIRECT as follows:

=HLOOKUP(A12,INDIRECT(C5),2,TRUE