How to use CELL contents in a FORMULA to look at specific WORKSHEET

[Improving]

Hello,

My first post. Tried searching but not even sure if I'm using the correct names.

What I'm trying to do is use a CELL contents (contains SHEET NAME) in a FORMULA.

Example:
Working in Sheet 1

B1= XX
A1 = Sheet 2

In Sheet 2
Range needed in Sheet 2 = A1:D4

What I want to do is have the VLOOKUP look in whatever Sheet name specified in A1
What notation do I need to enter to get this to work where the formula will look at whatever Sheet specified in cell A1?

In cell C1 type, what?

=VLOOKUP(B1,'Sheet 2'!A1:D4,3,FALSE) this is what I want but with Sheet 2 as a variable based on A1

I need "Sheet 2" to be whatever specified in cell A1, could be Sheet 3, Sheet 4, etc...

Hope this makes sense for those reading!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=VLOOKUP(B1,INDIRECT("'"&A1&"'!A1:D4"),3,FALSE)

 

[Improving]

AWESOME!

Thank you so very much.... I did see the INDIRECT item but not sure how it works. Will read up a bit more on that!

 

[Fluff]

You're welcome & thanks for the feedback.