logical test for an #N/A condition in a cell

[F.Rosario]

I have a VLOOKUP that returns a dollar value from another spreadsheet. If an
entry is not found, the cell value becomes #N/A, which affects the summary of
that column. I want to test for the #N/A condition and make it 0, but I
haven't figured out the syntax to compare the value of a cell to #N/A. Any
suggestions?

 

[CLR]

-IF(ISNA(YOURVLOOKUPFORMULA),0,YOURVLOOKUPFORMULA)

Vaya con Dios,
Chuck, CABGx3

 

[Laura Cook]

To test for #N/A, you can use "ISNA":

=IF(ISNA(VLOOKUP(A1,Sheet2!B1:C20,2,0)),0,VLOOKUP(A1,Sheet2!B1:C20,2,0))

Laura

 

[Gary''s Student]

This works for #N/A as well as other orrors:

=IF(ISERROR(A1),0,A1)

 

[F.Rosario]

Thank you very much, that solved my problem!

-IF(ISNA(YOURVLOOKUPFORMULA),0,YOURVLOOKUPFORMULA)

Vaya con Dios,
Chuck, CABGx3

 

[F.Rosario]

Thank you very much, that solved my problem as well!

 

[F.Rosario]

Thank you very much!

To test for #N/A, you can use "ISNA":

=IF(ISNA(VLOOKUP(A1,Sheet2!B1:C20,2,0)),0,VLOOKUP(A1,Sheet2!B1:C20,2,0))

Laura