<div>
First off, this part of your dynamic <i>vlookup </i>has to return a number <i>VLOOKUP(B1,$A$31:$C$45,2,F<wbr />ALSE)</i>. Then you should try <i>CONCATENATE </i>to put it all together like this:<br />
<br />

<pre><code id="code-20-41907570-1">=VLOOKUP(B11,CONCATENATE(&quot;'PNL'!$A&quot;,VLOOKUP(B1,$A$31:$C$45,2,FALSE),&quot;:N&quot;,VLOOKUP(B1,$A$31:$C$45,3,FALSE)),4,FALSE)</code></pre>
<br />
I can't test it very well, but it works for me like that.
</div>


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.

<div>
Hi Michael , when I put your version it brings the following when I do the Evaluate formula<br />
<br />
Vlookup(&quot;XLF&quot;,&quot;'PNL!$A14:N<wbr />38&quot;,4,FALS<wbr />E)<br />
<br />
But is still results #VALUE!
</div>


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!

<div>
<div class="content wysiwyg-content">
I have the following formula which is not working. <br />
<br />
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. <br />
<br />
=VLOOKUP(B11,&quot;'PNL'!A&quot;&amp;VLO<wbr />OKUP(B1,$A<wbr />$31:$C$45,<wbr />2,FALSE)&amp; &quot;:N&quot;&amp;VLOOKUP(B1,$A$31:$C$4<wbr />5,3,FALSE)<wbr />,4,FALSE)<br />
<br />
When I evaluate the formula, It shows Vlookup(&quot;XLF&quot;,&quot;'PNL'!A14:N<wbr />38&quot;,4,Fals<wbr />e) and then it gives #Value!<br />
<br />
The vlookup function that I create manually work fine (=VLOOKUP(B11,'PNL'!$A$14:<wbr />$N$38,4,FA<wbr />LSE)), 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?
</div>
</div>


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?

Dynamic Vlookup Function Formula Help

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.