How can I use a Cell reference to indicate to a VLOOKUP formula which Tab the array data is located on. I want to be able to control the tab the vlookup is using by indicating the tab name in a specified cell. Here's my attempt that failed. =VLOOKUP($A2,INDIRECT($G$1)!$A$1:$B$100,2,FALSE)

This would allow me to specify the name of the tab in cell G1. E.g. Tab1, Tab2.

Thanks in advance!

I do not know if this will work at all, but shouldn't the syntax be something like: =VLOOKUP($A2,INDIRECT($G$1)&"!"&$A$1:$B$100,2,FALSE)

MIKE

Hi Mike, Thanks for trying. The formula you sent back causes a circular reference for the array. for some reason Excel formula assumes the array is on the page where the formula is rather than the one being noted in the referential cell G1. Any other ideas?

Thanks,

Use the =INDIRECT() function to build the whole thing. In Cell G1 put your tab name = Sheet2

In Cell H1 put your beginning range number = A1

In Cell I1 put in your ending range number = B100Then build your formula:

=VLOOKUP($A2,INDIRECT(G1&"!"&$H1&":"&$I$1),2,FALSE)

See if that works for you.

MIKE

You-da-man! Works great. The power of this formula is that it will allow me to create dynamic VLOOKUPS. So if you have multiple tabs that you need to aggregate data from on to a single sheet, this formula can facilitate that functionality & permit you to do dynamic charts as well. Much appreciated Mike! If you're ever in LA, lunch is on me.

& the best part is I now have a better idea of the power of the INDIRECT function.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History