Referencing a different tab according to a formula result

[Guest]

Hi Team,
I have multiple tabs each with similar but different 2X2 reference data on
them.
On my user input sheet I want the user to input data that then calculates
which tab the rest of the formula should perform the index/vlookup/hlookup
on. I know about =&[Tab] function in the header/footer but can I do a
=[$C$2]! where $C$2 changes?
Thanks
Ditch

 

[T. Valko]

Need more info.

If cell C2 contains a sheet name then to use that in a VLOOKUP formula:

=VLOOKUP(A1,INDIRECT("'"&C2&"'!A:B"),2,0)

This is effectively:

=VLOOKUP(A1,Sheet_name!A:B,2,0)

 

[Guest]

You could use INDIRECT

Eg if you have this in C3:
=VLOOKUP(A2,Sheet3!A:B,2,0)

You could use INDIRECT to make the formula flexible
for the sheetname, viz.:
=VLOOKUP(A3,INDIRECT("'"&C$2&"'!A:B"),2,0)
where C2 would contain the required sheetname, ie: Sheet3, Sheet2, etc