Help with VLOOKUP formula

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
 
A

Arvi Laanemets

Hi

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

Arvi Laanemets
 
E

Earl Kiosterud

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.
 
Top