HLookup, multiple tables and range names in other cells

  • Thread starter Thread starter Matt Harris
  • Start date Start date
M

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
 
Hi Matt

if i'm understanding correctly try

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

Cheers
JulieD
 
Back
Top