Change lookup range depending on result of 2nd lookup

[sab_ya]

Hi,

am trying to vary the name of the lookup range depending o the result
of a 2nd lookup --
NYC =vlookup(A1,range1,4,0)
LA =vlookup(B1,range2,4,0)
..
..
..
SFO =vlookup(C1,rangeXXX,4,0)

Rather than having to manually edit the ranges I would prefer excel to
lookup the ranges by something like

NYC =vlookup(A1,vlookup(A1,RangeTable,2,0),4,0)

RangeTable:

NYC range1
LA range2
SFO rangeXXX

Have tried to nest two vlookups as above but am having no luck

Any suggestions?

Thanks,
sab_ya

 

[Tyro]

Of course not. You're saying lookup a value in a single value returned from
the interior vlookup and get the result from the 4th column of the single
value. I'm not proposing an answer. Just presenting this to you to think
about..

Tyro

 

[Henn Sarv]

You can play with functions like

Indirect
Offset

for example - the 1st lookup gives You name of range to use in 2nd lookup
and the name is converted to range using Indirect

check Help for closer hints

Henn

 

[sab_ya]

Thanks Henn, INDIRECT does the job!