# How can I replace #N/A with the value 0 using VLOOKUP

#### vidtec

##### New Member
I keep a complete inventory list in Excel XP (2002 ver), I have to include what our 40+ routes have on there trucks. They use computers to track there sales & inventory so I download there totals after they come in and upload there info. I querry a summery of there there inventorys and import it to a seperate sheet in the workbook. Then I use VLOOKUP to find the items and return the quanties to the inventory spreadsheet. If they don't have an item on there truck that we carry VLOOKUP returns a #N/A because it's not in the imported list and then that column will not add up. The numbers must be accurate so I have to use the false statment. Is there a way in the in the VLOOKUP formula to say if you can't find an item return a quanity of "0" instead of #N/A. The list is several hundred items long and changes daily I can't guarntee where in the imported data an item may or maynot be it has to search and look it up if it's there. The workaround I have been using is to manually find all the #N/A's and add them to the imported list and give them a quanity of "0" but this is time consuming and defeats the purpose of having the spreadsheet do the woke for you. Thank you for any help anyone may give.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Richard Schollar

##### MrExcel MVP
Sure, you can use an IF statement to do this:

Code:
``=IF(ISNA(VLOOKUP(A1,DataRange,2,FALSE)),"",VLOOKUP(A1,DataRange,2,FALSE))``

Modifying the VLOOKUP arguments as required.

Best regards

Richard

EDIT: this will show an empty string (ie "") if the lookup isn't matched. To result in a 0, just replace the "" with 0 in the above formula.

#### Barry Katcher

##### Well-known Member
...or
Code:
``=IF(ISNA(VLOOKUP(A1,DataRange,2,FALSE)),0,VLOOKUP(A1,DataRange,2,FALSE))``
if you want a zero instead of a blank.

#### vidtec

##### New Member
Thank You all very much this worked great. I tried the "IF" statement along with several others before but didn't have my I's dotted & T's crossed correctly. Works great now. This is a great message board can't wait for the book. :wink:

#### Barry Katcher

##### Well-known Member
Try this for your second problem.:
Book1
ABCD
1O.K.All I's are dottedAll t's are crossed
Sheet2

Formula in A1 is:
=IF(AND(B1="All i's are dotted",C1="All t's are crossed"),"O.K.","You forgot to dot your i's and cross your t's.")

Replies
5
Views
355
Replies
4
Views
425
Replies
8
Views
279
Replies
10
Views
241
Replies
4
Views
152

1,118,917
Messages
5,575,027
Members
412,635
Latest member
Arren