Getting Rid of Vlookup #N/A Error Message

**headley4ever** · 12-07-2010, 06:03 PM

Greetings Evreryone,

How can you write a VLOOKUP formula to prevent it from giving you an #N/A error message when it is unable to find the lookup value.

For example: If i am looking for let say number 101 in column 'A" but there is no number one, how do i get the vlookup formula to return Zero (0) instead of #N/A.

**ConneXionLost** · 12-07-2010, 06:11 PM

Test for the existence of the value before you use VLOOKUP.

Please Login or Register  to view this content.

**Cutter** · 12-07-2010, 06:23 PM

Or:

=IF(ISNA(your formula),0,your formula)

**headley4ever** · 12-07-2010, 07:12 PM

The formula i am using is =Vlookup(101,A1:C200,3,0) NB: 101 is the Item number for a Product we sell and this is in column "A" along with 200 other item numbers for other products we sell. This formula gives us the amount sold for the respective Products based on their lookup value which is the item numbers. The problem i am having is that if an item is not sold the item number for that product would not appear on the spreadsheet so i will get #N/A in the cells for any item that is not sold. I Need know how i can get VLOOKUP to return Zero (0) in the cells of these items that is not sold instead of #N/A

**Cutter** · 12-07-2010, 07:16 PM

By using what has been given.

=IF(ISNA(Vlookup(101,A1:C200,3,0)),0,Vlookup(101,A1:C200,3,0))

If you prefer the other method that was suggested adapt it thusly:

=IF(COUNTIF(A1:A200,A1)>0,VLOOKUP(A1,A1:C200,3,0),0)
or
=IF(COUNTIF(A1:A200,A1)=0,"",VLOOKUP(A1,A1:C200,3,0))
NOTE: A1 here would be the cell holding your 101 value