vlookup where sheet name=cell value

[DDawson]

Can you help me to set up a Vlookup to another sheet where the sheet name is
the taken from the cell value in cell (I2). For example:

=VLOOKUP(A2,'(I2)'!$A$2:$A$260,3,FALSE)

Dylan Dawson

 

[papou]

Hello Dylan
Use INDIRECT to build your reference ie:

=VLOOKUP(A2,INDIRECT(I2 & "'!$A$2:$A$260"),3,FALSE)

HTH
Cordially
Pascal

 

[DDawson]

Thanks P.

I fiddled about with it and got this to work:
=VLOOKUP(A2,INDIRECT("'"&I2 & "'!$A$2:$C$260"),3,FALSE)

I also tried to make it tidier. The other sheets are named after people and
the I:I column also refers to the same people names.

I tried creating a named Range called "Resource" =Left(I2,5) to pick up the
first five letters of the persons name/sheetname and I created dynamic ranges
with the same names for the ranges in the other sheets. I thought I would be
able to call "Resource" to refer to the different ranges, but, I get a
volatile result with the following formulae.
=VLOOKUP(A3,INDIRECT(Resource),3,FALSE)