Automatically use the right tab in a vlookup

[LadyHarper]

I have a spreadsheet, that needs to look up internal and external rates.

Currently I use Vlookup and tell it which tab to look for based on the project it is on. In my spreadsheet I have Column B that says the Tab reference.

Is there a Formula that I can set so it automatically uses the Vlookup in the correct tab?

=VLOOKUP($H485,'EX51'!$I$2:$S$57,11,FALSE) = the EX51 is where I am telling it what tab, looking to change that to check Cell B for the correct tab.

 

[dreid1011]

You can do that with

INDIRECT("'"&EX51&"'!$I$2:$S$57")

 

[LadyHarper]

I'm not sure if I am doing something wrong, as it is not working.

=VLOOKUP($H479,(look in Cell B479)$I$2:$S$57,11,FALSE)

Cell B479 with have the tab from across the bottom that I need it to look at for the vlookup reference.

 

[dreid1011]

=VLOOKUP($H479,INDIRECT("'"&B479&"'!$I$2:$S$57"),11,FALSE)

Does B479 ever change when you drag the formula?

 

[LadyHarper]

Still not working...

 

[LadyHarper]

B437 has EX54 in it, and I want it to find the tab across the bottom that say EX54 and vlookup the info from that tab... I hope I'm explaining it correctly

 

[dreid1011]

It's not helping that the cell containing the tab name seems to keep changing each time you reply.

=VLOOKUP($H479,INDIRECT("'"&B437&"'!$I$2:$S$57"),11,FALSE)

 

[LadyHarper]

For the B437 or B479... I have the right numbers in. I am sitting in Cell I437, and put in the formula = =VLOOKUP($H437,INDIRECT("'"&B437&"'!$I$2:$S$57"),11,FALSE)

Its giving me a result of #N/A, but if I do a normal vlookup, and just tell it tab EX54 then it finds it....

My B column changes to the correct tab name I want it to look under. As there are multiple tabs.

 

[dreid1011]

Are there any extra spaces in your tab names in Col B? I tested the formula (with different ranges) but the concept is working. So that tells me there is something wrong with the sheet names in Col B.

Book1
	A	B	C
15	EX41	F	Fig
16	EX51	A	Apple
Sheet1

Cell Formulas
Range	Formula
C15:C16	C15	=VLOOKUP($B15,INDIRECT("'"&A15&"'!$A$2:$B$5"),2,FALSE)

Book1
	A	B
2	E	Eggplant
3	F	Fig
4	G	Grapes
5	H	Hummus
EX41

Book1
	A	B
2	A	Apple
3	B	Banana
4	C	Citrus
5	D	Dragon Fruit
EX51

 

[LadyHarper]

=VLOOKUP($H437,INDIRECT("'"&B437&"'!$I$2:$S$57"),11,FALSE)

It looks right to me, so if you see something wrong pleasse let me know