VLOOKUP looking at another cell for its range

[Rich18144]

Hey all,

I was wondering (because I can't find any answers elsewhere) if the following is possible.

I would like a VLOOKUP to look at the value/text within another cell to determine which range to look in, i.e.

Cell A1
=VLOOKUP(10, (text in A2), 2, FALSE)

Cell A2 (text cell)
'Sheet Name'!$B$3:$F$11




Therefore, the final formula should read:

=VLOOKUP(10, 'Sheet Name'!$B$3:$F$11, 2, FALSE)

Any ideas if this is possible?

I am, essentially, just trying to make it so I only have to change one cell and a whole load of different LOOKUP formulae are changed.

Thank you for your time,

Rich