Variable VLOOKUP

[Assamita]

I don't know if it's possible, but I would like to do a "Variable" VLOOKUP. Let me put it in an example.

Imagine I have a workbook with sheets named "A", "B" and "C", and then a fourth sheet with a dropdown selectable, with values A, B and C.
In that fourth sheet there's also a VLOOKUP function and I'd like it to look at different tabs depending on the dropdown selection.

If the dropdown was in cell B1, it would be something like:

=VLOOKUP(A:A;[B]' & $B$1 & '[/B]!A:B;2;0)

Would that be possible somehow?

Thank you

 

[Joe4]

You need to use the INDIRECT function when building ranges dynamically, i.e.

=VLOOKUP(A1;INDIRECT(B1 & "!A:B");2;0)

 

[Assamita]

Thank you!! I need to start using INDIRECT more often. It has so many great capabilities...

 

[Joe4]

You are welcome!

Chip Pearson did a pretty good write-up on INDIRECT here: http://www.cpearson.com/excel/indirect.htm