removing an '#N/A' response from VLOOKUP

[Ian_G]

Hi all,

I am trying to create a formula using VLOOKUP where the returned value will be either the quantity found next to my array or if the VLOOKUP is unsuccessful, return a value of '0'.
<TABLE style="WIDTH: 505pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=672 border=0><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" span=4 width=86><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" span=2 width=83><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 121pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 41.25pt; BACKGROUND-COLOR: transparent" width=162 colSpan=2 height=55 rowSpan=3>Upper Assy</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=172 colSpan=2>X23424254</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=172 colSpan=2>X25143541</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 124pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=166 colSpan=2>X25492511</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8"> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">#N/A</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8"> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">#N/A</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8"> </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" colSpan=2 height=21>=SUM(B2+D2+F2)
</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" colSpan=2>#N/A</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" colSpan=2>#N/A</TD></TR></TBODY></TABLE>

In cell B2 my vlookups (which are a simple =VLOOKUP(B1,STOCK!A:B,2,0) will return '#N/A' when the array isn't found and then knock out all of my formulae along row 3.

Please help!

 

[VoG]

Try

=IF(ISNA(VLOOKUP(B1,STOCK!A:B,2,0)),0,VLOOKUP(B1,STOCK!A:B,2,0))

 

[Firefly2012]

Sounds to me like a SUMIF would suit you too (depending on whether you have any repeates in the Stock data):

=SUMIF(Stock!A:A,B1,Stock!B:B)

 

[Ian_G]

Spot on VoG! I was trying out =ISNA, but I was getting confused whilst trying to incorporate it (I kept getting a value of TRUE). This has worked a treat though.

Many thanks