How to use concatenate in a vlookup formula

[ron.bo]

I am trying to use "&" in a vlookup to do a lookup on a differant sheet that is listed in cell B1, the formula I created is =VLOOKUP(A7,"Sheet"&B1&"!"&"A1:J40",5,0), "Sheet"&B1&"!"&"A1:J40" = Sheet48!A1:J25 (with 48 in cell B1) which is exactly what it should be. But it returns #VALUE! error. What am I doing wrong?

Thanks, for any help

 

[Marcílio_Lobão]

ron.bo, Good evening.

Try to use: =VLOOKUP(A7, INDIRECT("Sheet" & B1 & "!" &"A1:J40"), 5,0)

Please, tell us if it worked for you.

I hope it helps.

 

[ron.bo]

Thank-you, that is perfect.

 

[Marcílio_Lobão]

ron.bo,

You're welcome and Glad to help you.