Can you use text in a cell to define a worksheet?

[MrStrawb]

I would like to know if it is possible to use text from a cell to define a worksheet name within a vlookup?

I have a power query with item product codes, each product code has its own worksheet containing details of the item

Currently I am manually changing the name of the sheet within the vlookup for each item however wondered if there is a way i can reference worksheet name via text in a cell i.e. =b$3 so that i can drag the formula across to automatically lookup cell c$3,c$4,c$5 etc?

Any advice would be greatly appreciated

Thanks in advance

Adam

 

[steve the fish]

You can use INDIRECT. Heres an example:

=VLOOKUP(A2,INDIRECT("'"&A1&"'!B:C"),2,0)

where the lookup value is in A2 and the sheet name you want to search is in A1.