Why am I getting the #N/A Error?

[JadonR]

I don't understand why I keep getting the #N/A error if there is no match for Y5 on the "Purchasing Units" sheet. I thought that is what the "EACH" at the end of the formula is for. Can someone explain what I'm doing wrong?

=IF(A$24="Yes",(VLOOKUP(Y5,'Purchasing Units'!A:B,2,FALSE)),"EACH")

 

[njimack]

The "EACH" is returned if A24 <> "Yes". Try this:
=IF(OR($A$24="Yes",COUNTIF('purchasing units'!A:A,Y5)=0),"EACH",VLOOKUP(Y5,'purchasing units'!A:B,2,FALSE))

By the way, referencing entire columns is inefficient - if you post a sample of your data, a more efficient function can be suggested.

 

[JadonR]

Thanks njimack, however, your formula returns "EACH" regardless of what is on the 'Purchasing Units' sheet in the vlookup. If A24 is set to YES, then I want the value in Y5 to be whatever is on the 'Purchasing Units' sheet. If there is no matching value, then I want it to be "EACH".

 

[markmzz]

Like this:


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>X</th><th>Y</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #FFFF00;;">ABC</td><td style="text-align: center;background-color: #FFFF00;;">1200</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #FFFF00;;">DEF</td><td style="text-align: center;background-color: #FFFF00;;">3200</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #FFFF00;;">GHT</td><td style="text-align: center;background-color: #FFFF00;;">4350</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #FFFF00;;">YUI</td><td style="text-align: center;background-color: #FFFF00;;">1000</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #FFFF00;;">EWQ</td><td style="text-align: center;background-color: #FFFF00;;">2398</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">OPI</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">ABC</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;background-color: #FFFF00;;">Yes</td><td style="text-align: center;background-color: #FFFF00;;">No</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">EACH</td><td style="text-align: center;background-color: #FFFF00;;">----</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;background-color: #FFFF00;;">Yes</td><td style="text-align: center;background-color: #FFFF00;;">No</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">1200</td><td style="text-align: center;background-color: #FFFF00;;">----</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D24</th><td style="text-align:left">=IF(<font color="Blue">A$24="Yes",IF(<font color="Red">ISERROR(<font color="Green">VLOOKUP(<font color="Purple">Y5,$A$1:$B$6,2,0</font>)</font>),"EACH",VLOOKUP(<font color="Green">Y5,$A$1:$B$6,2,0</font>)</font>),"----"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E24</th><td style="text-align:left">=IF(<font color="Blue">B$24="Yes",IF(<font color="Red">ISERROR(<font color="Green">VLOOKUP(<font color="Purple">Z5,$A$1:$B$6,2,0</font>)</font>),"EACH",VLOOKUP(<font color="Green">Z5,$A$1:$B$6,2,0</font>)</font>),"----"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D25</th><td style="text-align:left">=IF(<font color="Blue">A$24="Yes",IF(<font color="Red">ISERROR(<font color="Green">VLOOKUP(<font color="Purple">Y6,$A$1:$B$6,2,0</font>)</font>),"EACH",VLOOKUP(<font color="Green">Y6,$A$1:$B$6,2,0</font>)</font>),"----"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E25</th><td style="text-align:left">=IF(<font color="Blue">B$24="Yes",IF(<font color="Red">ISERROR(<font color="Green">VLOOKUP(<font color="Purple">Z6,$A$1:$B$6,2,0</font>)</font>),"EACH",VLOOKUP(<font color="Green">Z6,$A$1:$B$6,2,0</font>)</font>),"----"</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[markmzz]

A small modification (Z5 and Z6):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>X</th><th>Y</th><th>Z</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #FFFF00;;">ABC</td><td style="text-align: center;background-color: #FFFF00;;">1200</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #FFFF00;;">DEF</td><td style="text-align: center;background-color: #FFFF00;;">3200</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #FFFF00;;">GHT</td><td style="text-align: center;background-color: #FFFF00;;">4350</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #FFFF00;;">YUI</td><td style="text-align: center;background-color: #FFFF00;;">1000</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #FFFF00;;">EWQ</td><td style="text-align: center;background-color: #FFFF00;;">2398</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">OPI</td><td style="text-align: center;background-color: #FFFF00;;">OPI</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">ABC</td><td style="text-align: center;background-color: #FFFF00;;">ABC</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;background-color: #FFFF00;;">Yes</td><td style="text-align: center;background-color: #FFFF00;;">No</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">EACH</td><td style="text-align: center;background-color: #FFFF00;;">----</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;background-color: #FFFF00;;">Yes</td><td style="text-align: center;background-color: #FFFF00;;">No</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">1200</td><td style="text-align: center;background-color: #FFFF00;;">----</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D24</th><td style="text-align:left">=IF(<font color="Blue">A$24="Yes",IF(<font color="Red">ISERROR(<font color="Green">VLOOKUP(<font color="Purple">Y5,$A$1:$B$6,2,0</font>)</font>),"EACH",VLOOKUP(<font color="Green">Y5,$A$1:$B$6,2,0</font>)</font>),"----"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E24</th><td style="text-align:left">=IF(<font color="Blue">B$24="Yes",IF(<font color="Red">ISERROR(<font color="Green">VLOOKUP(<font color="Purple">Z5,$A$1:$B$6,2,0</font>)</font>),"EACH",VLOOKUP(<font color="Green">Z5,$A$1:$B$6,2,0</font>)</font>),"----"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D25</th><td style="text-align:left">=IF(<font color="Blue">A$24="Yes",IF(<font color="Red">ISERROR(<font color="Green">VLOOKUP(<font color="Purple">Y6,$A$1:$B$6,2,0</font>)</font>),"EACH",VLOOKUP(<font color="Green">Y6,$A$1:$B$6,2,0</font>)</font>),"----"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E25</th><td style="text-align:left">=IF(<font color="Blue">B$24="Yes",IF(<font color="Red">ISERROR(<font color="Green">VLOOKUP(<font color="Purple">Z6,$A$1:$B$6,2,0</font>)</font>),"EACH",VLOOKUP(<font color="Green">Z6,$A$1:$B$6,2,0</font>)</font>),"----"</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[JadonR]

Thanks markmzz. I simply modified it to what I need and it works perfectly!

=IF(A$24="Yes",IF(ISERROR(VLOOKUP(Y3,'Purchasing  Units'!$A$1:$B$18,2,0)),"EACH",VLOOKUP(Y3,'Purchasing  Units'!$A$1:$B$18,2,0)),"EACH")

By the way, what do you use to show the nice excel sheet illustrations?

 

[markmzz]

Thanks markmzz. I simply modified it to what I need and it works perfectly!

=IF(A$24="Yes",IF(ISERROR(VLOOKUP(Y3,'Purchasing  Units'!$A$1:$B$18,2,0)),"EACH",VLOOKUP(Y3,'Purchasing  Units'!$A$1:$B$18,2,0)),"EACH")

By the way, what do you use to show the nice excel sheet illustrations?

JadonR,

Thanks for the feedback.

And use the link below for helper to post.

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

Markmzz