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

[BobBridges]

Yes, you can. This is what the INDIRECT function is for. You'd just do it this way:

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

(Pause to check for syntax errors....) Yep, I think that's right. The INDIRECT function, as you may have figured out, takes a character string and interprets it as an address or range, which is what you want here. Give it a shot, and let me know if it doesn't work, but I think it's right.