Microsoft Office Tutorials and References

In Depth Information

IFERROR was introduced in Excel 2007. For compatibility with previous versions, use this formula:

=IF(ISERROR(C4/B4),””,C4/B4)

#N/A errors

The #N/A error occurs if any cell referenced by a formula displays #N/A.

Some users like to enter =NA() or #N/A explicitly for missing data (that is, Not Avail-

able). This method makes it perfectly clear that the data is not available and hasn't been

deleted accidentally.

The #N/A error also occurs when a lookup function (HLOOKUP, LOOKUP, MATCH, or VLOOKUP) can't

find a match.

If you would like to display an empty string instead of #N/A, use the IFNA function in a formula like this:

=IFNA(VLOOKUP(A1,C1:F50,4,FALSE),””)

The IFNA function is new to Excel 2013. For compatibility with previous versions, use a

formula like this:

=IF(ISNA(VLOOKUP(A1,C1:F50,4,FALSE)),””,VLOOKUP(A1,C1:F50,4,FALSE))

#NAME? errors

The #NAME? error occurs under these conditions:

• The formula contains an undefined range or cell name.

• The formula contains text that Excel
interprets
as an undefined name. A misspelled function name, for ex-

ample, generates a #NAME? error.

• The formula contains text that isn't enclosed in quotation marks.

• The formula contains a range reference that omits the colon between the cell addresses.

• The formula uses a worksheet function that's defined in an add-in, and the add-in is not installed.