range names in lookup functions

[griffith]

I am trying to incorporate range names in an Hlookup function so the user can select which lookup table to use.

My Hlookup function is:
=hlookup(E14,G14,L13)

Where G14 is a user enterd value. Possible values are Table1, Table2, or Table3

The spreadsheet contains range names of

Table1 (range = ah17:an38)
Table2 (range = ah43:an64)
Table3 (range = ah69:an90)

Excel does not appear to use the text entry in G14 to the identical range name built into the spreadsheet.

Any help will be appreciated. Thanks


Duane

[swatsp0p]

You don't mention what the L13 reference in your formula represents. Is that a cell reference that will contain the row index number?

HLOOKUP syntax is:

HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

Assuming all of the above is true, your formula should look like this:

=HLOOKUP(E14,INDIRECT(G14),L13)

HTH

Bruce