Vlookup dynamically changes for different ranges

J

Jeff

I have a vlookup that needs to look at different tables.

Is there any way to automatically change the range it looks up, without if
lloops. So if you put a 1 in cell A1 it would look up the first range, 2 the
second range and so on. I tried using choose but this did not work.

Thanks
 
N

Niek Otten

You need the INDIRECT() function. Look in HELP for details; if you can't
sort it out, post again!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
D

Dave Peterson

One way (for 1 to 3):

=VLOOKUP(A2,CHOOSE(A1,INDIRECT("sheet1!a1:b999"),
INDIRECT("sheet2!a1:b999"),
INDIRECT("sheet3!A1:b999")),2,FALSE)
(all one cell)
 
Top