Change lookup range depending on result of 2nd lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sab_ya - 07 Mar 2008 04:12 GMT

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

Reply to this Message

Tyro - 07 Mar 2008 04:24 GMT

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

> Hi,
>
[quoted text clipped - 24 lines]
> Thanks,
> sab_ya

Reply to this Message

Henn Sarv - 07 Mar 2008 07:29 GMT

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

> Hi,
>
[quoted text clipped - 24 lines]
> Thanks,
> sab_ya

Reply to this Message

sab_ya - 11 Mar 2008 00:55 GMT

Thanks Henn, INDIRECT does the job!

Reply to this Message

Rate this thread: