Help dealing with "#N/A" (in general, but specifically with VLOOKUP)

S

shadestreet

Are there some tips for handling the #n/a errors? They really screw u
my spreadsheets, but sometimes the outputs are unavoidable. Wha
irritates me most is that Excel doesn't seem to acknowledge them as an
number, not even zero.

For example, I may have a list of values that I want to add, and i
there is a #n/a in the list, the summation will not work. Worse yet
even a SUMIF doesn't seem to work.

Are there ways to quickly remove, or have excel just put a zero in th
cell instead?

As for a specific problem, I am using VLOOKUP right now on an arra
with 2000 items in column A. The array that VLOOKUP is searchin
through only has 1500 items, so obviously there are 500 items from th
first list that do not have a matching value. Instead of leaving th
cell blank, excel puts in the #n/a, which screws up further functions
wanted to carry out. Is there an easy to re-write my VLOOKUP statemen
so that if there is not a value to match, it simply leaves the cel
blank instead of dropping in the error?

Thanks for any suggestions
 
A

Alan

Are there ways to quickly remove, or have excel just put a zero in the
cell instead?
Yes there are, you need to wrap your formulas in an error trap such as
(example formula)
=IF(ISNA(VLOOKUP(D2,H3:I5,2,FALSE)),0,VLOOKUP(D2,H3:I5,2,FALSE))
or
=IF(ISERROR(VLOOKUP(D2,H3:I5,2,FALSE)),0,VLOOKUP(D2,H3:I5,2,FALSE))
You can change the result to your requirements eg
=IF(ISERROR(VLOOKUP(D2,H3:I5,2,FALSE)),"Invalid
Entry",VLOOKUP(D2,H3:I5,2,FALSE))
etc,
Regards,
 
Top