# 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?

Commented:
"'PNL'!A14:N38" is a text, not a range.
To convert to a range, use the indirect function.
So the formula must be

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

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:

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

I can't test it very well, but it works for me like that.
0

Author Commented:
Hi Michael , when I put your version it brings the following when I do the Evaluate formula

Vlookup("XLF","'PNL!\$A14:N38",4,FALSE)

But is still results #VALUE!
0

Author Commented:
Excellent
0
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.