VLOOKUP: using a text reference for the table array

[stanelburger]

Hello,
I currently have the formula: =VLOOKUP($B4,"Data!B"&C$2+2&":Data!D"&C$2+4,2,FALSE)
which returns a #VALUE! error.

I do not get the error if I substitute the second argument of VLOOKUP with what would have been returned: =VLOOKUP($B4,Data!B3:D5,2,FALSE).

Does anyone know how I could fix this?

Thank you.

 

[mikerickson]

You could use INDIRECT, as in
=VLOOKUP($B4, INDIRECT("Data!B"&(C$2+2)&":D"&(C$2+4)), 2, FALSE)

Or you could use INDEX, as in
=VLOOKUP($B4, INDEX(Data!C:D, C$2+2, 1):INDEX(Data!C:D, C$2+4, 2), 2, FALSE)

 

[stanelburger]

Thank you very much. It works perfectly now.