are variable table-array names in functions possible?
 

Is it possible somehow to use variable table array names in VLookup or
HLookup? I would like to create a drop down list of tables, then use the
look-up function against the table designated for example =HLOOKUP(D6,,2)
where Scenario is a cell reference to the selection from the drop down list.
All I am able to get is an #N/A error. The HLOOKUP function works as
expected if I replace the cell reference (ie. Scenario) with an actual table
name (eg. Scenario1).

JimH

Sorry the formula that doesn't work should say =HLOOKUP(D6,Scenario,2) where
Scenario is a cell reference to the selection from the drop down list


"JimH" wrote:

Is it possible somehow to use variable table array names in VLookup or
HLookup? I would like to create a drop down list of tables, then use the
look-up function against the table designated for example =HLOOKUP(D6,,2)
where Scenario is a cell reference to the selection from the drop down list.
All I am able to get is an #N/A error. The HLOOKUP function works as
expected if I replace the cell reference (ie. Scenario) with an actual table
name (eg. Scenario1).

JimH

Try

=HLOOKUP(D6,INDIRECT(Scenario),2)


"JimH" wrote:

Sorry the formula that doesn't work should say =HLOOKUP(D6,Scenario,2) where
Scenario is a cell reference to the selection from the drop down list


"JimH" wrote:

Is it possible somehow to use variable table array names in VLookup or
HLookup? I would like to create a drop down list of tables, then use the
look-up function against the table designated for example =HLOOKUP(D6,,2)
where Scenario is a cell reference to the selection from the drop down list.
All I am able to get is an #N/A error. The HLOOKUP function works as
expected if I replace the cell reference (ie. Scenario) with an actual table
name (eg. Scenario1).

JimH