Help with VLOOKUP formula

  • Thread starter Thread starter WF
  • Start date Start date
W

WF

I'm using the VLOOKUP formula to pull results from another spreadsheet. If
the VLOOKUP cannot find the value in returns "#N/A." This "#N/A" not only
looks goofy, but it prevents me from summing the column. How can I turn the
"#N/A" into a zero?

Many thanks for your help!!

WF
 
Hi

=IF(ISERROR(VLOOKUP(...)),0,VLOOKUP(...))
=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))
=IF(ISERROR(VLOOKUP(...)),"",VLOOKUP(...))
etc.

Arvi Laanemets
 
WF,

=IF(ISNA(VLOOKUP(E2, Table, 2)), 0, VLOOKUP(E2, Table, 2))

A preferable approach is to put your original VLOOKUP into a hidden column,
and use:

=IF(ISNA(VlookupFormulaCell), 0, VlookupFormulaCell)

This approach doesn't require to evaluate the VLOOKUP twice, and the
formulas are smaller, and intermediate results can be examined. Some will
argue that this isn't a good approach, but reliability and maintainability
are more important than compactness.
 
Back
Top