Part II: Working with Formulas and Functions
# N/A e r r or s
The #N/A error occurs if any cell referenced by a formula displays #N/A.Some users like to use =NA() or #N/A explicitly for missing data. 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 was introduced in 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 example, 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.
Excel has a bit of a problem with range names. If you delete a name for a cell or a range and the name is used in a
formula, the formula continues to use the name, even though it’s no longer defined. As a result, the formula displays
#NAME?. You might expect Excel automatically to convert the names to their corresponding cell references, but this
doesn’t happen.
#NULL! errors
A #NULL! error occurs when a formula attempts to use an intersection of two ranges that
don’t actually intersect. Excel’s intersection operator is a space. The following formula, for
example, returns #NULL! because the two ranges don’t intersect:
=SUM(B5:B14 A16:F16)