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

[vidtec]

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.

 

[Richard Schollar]

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

=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]

...or

=IF(ISNA(VLOOKUP(A1,DataRange,2,FALSE)),0,VLOOKUP(A1,DataRange,2,FALSE))

if you want a zero instead of a blank.

 

[vidtec]

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]

Try this for your second problem.:

Book1
	A	B	C	D
1	O.K.	All I's are dotted	All 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.")