DIsplay a value of the cell calculation is #N/A

[Guest]

I have numerious calculations occuring in cells. Many times if certain values
cannot be calculted, #N/A appears. I would like the field to display "Null"
or "Nothing" if the error appears. How can this be done? I want a different
display instead of #N/A.

 

[JE McGimpsey]

One way:

Assuming your original formula is something like:

=VLOOKUP(A1,J:K,2,FALSE)

you can trap the error:

=IF(ISNA(MATCH(A1,J:J,FALSE)),"Nothing",VLOOKUP(A1,J:K,2,FALSE))

 

[Gord Dibben]

Add the ISNA function.

e.g.

=IF(ISNA(VLOOKUP(H9,$C$2:$F$35,3,FALSE)),"",VLOOKUP(H9,$C$2:$F$35,3,FALSE))

Will return a blank looking cell if result is #N/A

For "Null" type Null between the double quotes in formula


Gord Dibben MS Excel MVP

 

[JW]

I have numerious calculations occuring in cells. Many times if certain values
cannot be calculted, #N/A appears. I would like the field to display "Null"
or "Nothing" if the error appears. How can this be done? I want a different
display instead of #N/A.

=IF(ISNA(yourCalculation),"",yourCalculation)
or
=IF(ISNA(yourCalculation),"Null",yourCalculation)