How do I replace a return value of #N/A in a vlookup with zero?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero?

Without being able to do this I am unable sum the returned values.
 
Hi,

To sum the values ignoring the error values, you may want to use the
following formula. You need not alter your vlookup formula

In range A1:A4, you have the following

1
2
3
#DIV/0!

In cell A6, array enter (Ctrl+Shift+Enter) the following formula

SUM(IF(NOT(ISERROR(A1:A4)),A1:A4))

Regards,

Ashish Mathur
 
1.

=SUM(Range,"<>#N/A")

2. If you're sending the workbook across countries...

=SUMIF(Range,"<>"&NA())
 
Back
Top