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

vidtec

New Member
Joined
Dec 31, 2005
Messages
8
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. :biggrin:
 

Some videos you may like

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Feb 25, 2002
Messages
4,053
...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
Joined
Dec 31, 2005
Messages
8
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
Joined
Feb 25, 2002
Messages
4,053
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.")

:LOL: :LOL:
 

Watch MrExcel Video

Forum statistics

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