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.
