Referencing a sheet in VLOOKUP

[Guest]

Currently, I have a cell referencing a table on another sheet like so:

=VLOOKUP(A5,'Sheet2'!$J$4:$J$1998,1,TRUE)

But what I want to do is instead of referencing the sheet explicitly (i.e.
Sheet2), I'd like to reference a sheet based on another cell's value. E.g.:

=VLOOKUP(A5,'A2'!$J$4:$J$1998,1,TRUE)
where A2 would have the value "Sheet2".

Is this possible within excel?

 

[Guest]

Look at the indirect formula. It will allow you to change the sheet
references. Just keep in mind that indirect is a volitile function meaning
that it will recalculate every time a calculation occures. If you have
thousands of these formulas then the performance will drop off significantly
(especially since Vlookup is a slow formula).

If that is the case then you might want to use a named range and just change
the named range reference to keep the performance up.

 

[Dave Peterson]

=vlookup(a5,indirect("'" & a2 & "'!j4:j1998"),1,true)

 

[Tom Ogilvy]

=VLOOKUP(A5,Indirect("'" & A2 & "'!$J$4:$J$1998"),1,TRUE)