MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Removing #N/A after an exact match vlookup function


Posted by Brian on September 24, 2001 8:49 AM

I am doing a vlookup on sales dollars, when an exact match is made the $ are displayed. When there is not an exact match on sale the #N/A is displayed. I do not want to copy paste special to values and replace the N/A's but nest an if?? statement or something to return $0.00 instead of N/A.

Any ideas???


Posted by IML on September 24, 2001 8:55 AM

There are a few ways of doing this, one of which is,
=IF(COUNTIF(A1:A2,"bird"),VLOOKUP("bird",A1:B2,2,FALSE),0)

If you are looking up "bird" in column A in the A1:B2 range.

Good luck

Posted by Brian on September 24, 2001 9:58 AM

I do not understand - bird???

Posted by Brian on September 24, 2001 10:03 AM

I do not understand - bird???

Posted by IML on September 24, 2001 10:09 AM

Sorry, didn't mean to give you the bird, it is just an example. Replace "bird" with what you are looking up or the cell reference of what you are looking up.
If you are looking up the value in cell Z1, it would be:
=IF(COUNTIF($A$1:$A$2,Z1),VLOOKUP(Z1,$A$1:$B$2,2,FALSE),0)

Posted by Brian on September 24, 2001 10:14 AM

here is what my formula is now
what do i add???


=VLOOKUP(A2,'Product Sales'!A2:G5346,7,FALSE)

Posted by dan on September 24, 2001 10:20 AM

If you want the cell to show 0 if an exact match is not found do the following: =if(iserror(VLOOKUP(A2,'Product Sales'!A2:G5346,7,FALSE)),0,VLOOKUP(A2,'Product Sales'!A2:G5346,7,FALSE))

you could replace 0 with a phrase as long as quotes are around it ,"No Match", would be an example

Posted by Aladin Akyurek on September 24, 2001 10:33 AM

Adapt IML's proposal as to your formula as:

=IF(COUNTIF('Product Sales'!A2:A5346,A2),'Product Sales'!A2:G5346,7,0),0)

Aladin


Posted by IML on September 24, 2001 11:25 AM

There a little typo (paste-o ) in Aladin's. Try
=IF(COUNTIF('Product Sales'!$A$2:$A$5346,A2),VLOOKUP(A2,'Product Sales'!$A$2:$G$5346,7,0),0)
or Aladin would probably prefer
=IF(ISNUMBER(MATCH(A2,'Product Sales'!$A$2:$A$5346,0)),VLOOKUP(A2,'Product Sales'!$A$2:$G$5346,7,0),0)


Posted by Aladin Akyurek on September 24, 2001 12:22 PM

Yep, Darn it! or Aladin would probably prefer

Ian --

Although this one is telling more, I come to appreciate the shorter COUNTIF version.