Dynamic Vlookup Function Formula Help

I have the following formula which is not working.

So I am trying to assign the vlookup array location dynamically. I think I am having a problem with the worksheet name (PNL) in this case.

=VLOOKUP(B11,"'PNL'!A"&VLOOKUP(B1,$A$31:$C$45,2,FALSE)& ":N"&VLOOKUP(B1,$A$31:$C$45,3,FALSE),4,FALSE)

When I evaluate the formula, It shows Vlookup("XLF","'PNL'!A14:N38",4,False) and then it gives #Value!

The vlookup function that I create manually work fine (=VLOOKUP(B11,'PNL'!$A$14:$N$38,4,FALSE)), so the data is there. But my dynamic formula doesn't work. I think the problem is with the name of the spreadsheet. Or maybe something else?
Who is Participating?
Ejgil HedegaardConnect With a Mentor Commented:
"'PNL'!A14:N38" is a text, not a range.
To convert to a range, use the indirect function.
So the formula must be

Mike in ITIT System AdministratorCommented:
First off, this part of your dynamic vlookup has to return a number VLOOKUP(B1,$A$31:$C$45,2,FALSE). Then you should try CONCATENATE to put it all together like this:


Open in new window

I can't test it very well, but it works for me like that.
awesomejohn19Author Commented:
Hi Michael , when I put your version it brings the following when I do the Evaluate formula


But is still results #VALUE!
awesomejohn19Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.