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

Yes, you can. This is what the INDIRECT function is for. You'd just do it this way:(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.