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