Using text from cell in a range lookup

[abergman]

I'm doing a vlookup across multiple tabs and I have one summary sheet.
All of the lookup values are listed on the summary tab along with their tab
designation (ie A1 = Doe, John, A2 = MW Region).
Would it be possible to do a vlookup in this summary tab using these
references? For example, would it be possible to do vlookup(A1,
A2!$A$23:$D$320,4,false)
I want to be able to have the vlookup search in the right tab without having
to change the formula for each section.
Thank you!

 

[Luke M]

You're going to need to nest your formula with INDIRECT
You could use the VLOOKUP to get the Sheet name, and then plug that into the
INDIRECT command.
Example of nested formula
=IF(INDIRECT("'"&VLOOKUP(A1,
A2!$A$23:$D$320,4,false)"&'!A5>2,"Cell A5 is greater than 2",FALSE)

Not knowing exactly what you want to happen, hopefully this helps

 

[JBeaucaire]

Your formula becomes

=VLOOKUP(A1,INDIRECT("'"&A2&"'!$A$23:$D$320"),4,false

The name in cell A2 must be EXACTLY the same as the sheetname for thi
to work