Excel IFERROR and IFNA Functions

last modified October 5, 2026

The IFERROR and IFNA functions replace Excel error values with results of your choosing. IFERROR catches every error type, while IFNA catches only the #N/A error. This tutorial explains the difference between the two and shows practical examples of each. You will learn the syntax, the error types involved, and when to prefer one function over the other.

IFERROR and IFNA Function Basics

Both functions test a value and return a fallback when that value evaluates to an error. The tested value can be a formula, a cell reference, or a literal, and the fallback can be text, a number, a formula, or a blank. Both arguments are required.

Function	Syntax	Return Value
IFERROR	=IFERROR(value, value_if_error)	value_if_error for any error; otherwise value
IFNA	=IFNA(value, value_if_na)	value_if_na for #N/A only; otherwise value

IFERROR was introduced in Excel 2007 and IFNA in Excel 2013. Before IFERROR existed, the same effect required a nested IF together with ISERROR.

Argument	Meaning
value	The expression to check for an error. It can be a formula, a cell reference, or a literal value.
value_if_error	The value returned by IFERROR when value produces any error. It can be text, a number, a formula, or a blank.
value_if_na	The value returned by IFNA when value produces the #N/A error. Other errors are not caught.

Omitting the second argument is a syntax error. Excel replies "You've entered too few arguments for this function" and refuses to accept the formula.

IFERROR vs IFNA Comparison

The two functions behave identically for #N/A and differ for every other error. The following table shows what each function returns for the seven Excel error values.

Error	IFERROR result	IFNA result
#N/A	value_if_error	value_if_na
#VALUE!	value_if_error	#VALUE! unchanged
#REF!	value_if_error	#REF! unchanged
#DIV/0!	value_if_error	#DIV/0! unchanged
#NUM!	value_if_error	#NUM! unchanged
#NAME?	value_if_error	#NAME? unchanged
#NULL!	value_if_error	#NULL! unchanged

IFERROR is a broad safety net, while IFNA hides only "value not available" errors and lets every other error surface so it can be fixed. The next formula block shows the difference in a single line.

=IFERROR(1/0, "x")    ' x
=IFERROR(NA(), "x")   ' x
=IFNA(1/0, "x")       ' #DIV/0!, not caught
=IFNA(NA(), "x")      ' x

The first two formulas return "x" because IFERROR catches the division error and the #N/A error alike. The third formula keeps #DIV/0! because IFNA ignores it, and only the fourth formula, which receives #N/A, returns "x".

Basic IFERROR Example

This example shows how to handle division errors gracefully using IFERROR.

A	B	C
10	2	=IFERROR(A1/B1, "Error")
10	0	=IFERROR(A2/B2, "Error")

The table demonstrates division operations where the second row would normally result in a #DIV/0! error. IFERROR catches this and returns "Error" instead.

=IFERROR(A2/B2, "Error")

This formula divides A2 by B2 but returns "Error" if the division fails. The first row returns 5 (10/2) while the second returns "Error" (10/0).

IFERROR with VLOOKUP

IFERROR is commonly used with VLOOKUP to handle cases where lookup values aren't found. This example demonstrates this practical application.

A	B	C	D
101	Apple	105	=IFERROR(VLOOKUP(C1,A1:B3,2,FALSE),"Not Found")
102	Orange
103	Banana

The table shows a product lookup scenario. The formula in D1 searches for ID 105, which doesn't exist in the table. Without IFERROR, this would return #N/A.

=IFERROR(VLOOKUP(C1,A1:B3,2,FALSE),"Not Found")

This formula attempts to find product ID 105 in column A and return its name from column B. When not found, it returns "Not Found" instead of #N/A. This makes the output more user-friendly. Because IFERROR also hides typos that produce #NAME?, many writers prefer IFNA for lookups.

IFNA with XLOOKUP

IFNA is ideal for modern lookup functions like XLOOKUP when you only want to handle #N/A errors specifically. This example shows its usage.

A	B	C	D
NY	New York	CA	=IFNA(XLOOKUP(C1,A1:A3,B1:B3),"State not found")
TX	Texas
FL	Florida

The table demonstrates state code lookups. The formula searches for "CA" which isn't in the list, triggering the IFNA fallback value.

=IFNA(XLOOKUP(C1,A1:A3,B1:B3),"State not found")

This formula looks up state code CA and returns "State not found" when absent. Unlike IFERROR, other errors such as #VALUE! would still show, making IFNA more precise for lookup operations. XLOOKUP is available only in Excel 2021 and Microsoft 365.

Errors Caught by IFERROR

IFERROR recognizes all seven Excel error values. The table lists each one with the situation that typically produces it.

Error	Typical Cause
#N/A	A lookup cannot find the requested value
#VALUE!	The wrong type of argument, such as text where a number is expected
#REF!	A formula refers to a cell that was deleted
#DIV/0!	Division by zero or by an empty cell
#NUM!	An invalid numeric value, such as the square root of a negative number
#NAME?	A function name or range name is misspelled
#NULL!	An intersection of two ranges that do not actually intersect

IFNA reacts to the first row only. If any other error reaches IFNA, it is returned unchanged, which is exactly what you want when you intend to hide missing lookup values but still see genuine mistakes.

Blank Cell Behavior

An empty cell is not an error, so neither function replaces it. When the tested value refers to a blank cell, the result is 0 because the empty cell is treated as zero. An empty string written as "" is likewise not an error and is returned as a blank.

A	B
	=IFERROR(A1,"Missing")

=IFERROR(A1, "Missing")   ' 0 when A1 is empty
=IFNA(A1, "Missing")      ' 0 when A1 is empty

To turn an empty cell into a message, test it first, for example with =IF(A1="","Missing",IFERROR(A1,"Error")). Also note that when the fallback itself is an empty string, an error simply disappears without any message.

Nested IFERROR for Multiple Calculations

IFERROR can be nested to attempt multiple calculations until one succeeds. This example shows a fallback calculation approach.

A	B	C
10	0	=IFERROR(A1/B1,IFERROR(SQRT(A1),"Calculation failed"))

The table demonstrates a nested error handling approach. The formula first attempts division, then falls back to square root if division fails, and finally shows a custom message if both fail.

=IFERROR(A1/B1,IFERROR(SQRT(A1),"Calculation failed"))

This formula first tries to divide A1 by B1, which returns #DIV/0! because B1 is 0. The inner IFERROR then calculates the square root of A1 and returns 3.16 (the square root of 10). Only if that second calculation also failed would the message "Calculation failed" appear. This creates a robust calculation chain.

IFERROR with Array Formulas

IFERROR can wrap dynamic array functions to handle errors in their results. When FILTER finds no matching rows, it returns a #CALC! error rather than an empty array, and IFERROR can replace that error with a message.

A	B	C
100	Invalid	=IFERROR(FILTER(A1:A3,B1:B3="Valid"),"No valid data")
200	Invalid
300	Invalid

The table shows how IFERROR can handle errors in array formulas. Because no row is marked "Valid", FILTER has nothing to return and produces #CALC!, which IFERROR catches.

=IFERROR(FILTER(A1:A3,B1:B3="Valid"),"No valid data")

This formula filters column A for rows marked "Valid" in column B. Since none match, the result is the message "No valid data" instead of an error. When at least one row does match, FILTER spills the matching values and IFERROR passes them through unchanged. FILTER requires Excel 2021 or Microsoft 365.

Choosing Between IFERROR and IFNA

While both functions handle errors, they serve different purposes. IFERROR catches all error types, while IFNA only catches #N/A errors. The choice depends on your need for specificity.

Scenario	Recommended Function	Reason
VLOOKUP/XLOOKUP	IFNA	Only need to handle #N/A from lookup failures
Complex calculations	IFERROR	Multiple potential error types possible
Data validation	IFNA	Want other errors to remain visible

This decision table helps choose between the functions based on use case. IFNA is better when you want to preserve other error types for debugging, while IFERROR provides complete error suppression.

IFERROR and IFNA Errors and Troubleshooting

Cause	Symptom	Fix
Only one argument supplied	Too few arguments error	Supply both value and the fallback
Fallback is an empty string ""	Error vanishes with no message	Use a visible message when the blank is confusing
IFERROR wraps a misspelled function	A real bug is reported as a normal value	Use IFNA so #NAME? and other errors stay visible
IFNA used on a division	#DIV/0! still shown	Use IFERROR if every error type should be caught
Lookup value has extra spaces or wrong type	#N/A shown for a value that exists	Clean the lookup value, for example with TRIM
value references an empty cell	Fallback is not returned	Empty cells are not errors; test for blank first
FILTER include range is the wrong size	#VALUE! or wrong spill result	Match the include range height to the array

The most common mistake is reaching for IFERROR everywhere and hiding errors that should be fixed. Prefer IFNA for lookups so unexpected errors remain visible, and reserve IFERROR for calculations that can genuinely fail in several ways.

The IFERROR and IFNA functions are essential tools for creating professional, user-friendly spreadsheets. They prevent error messages from disrupting workflows and make formulas more resilient. Remember the division of labor: IFERROR is the broad catch-all for the seven Excel error values, while IFNA is the precise tool that handles only #N/A and leaves every other problem on display. Choosing the right one keeps your reports clean without concealing mistakes you still need to fix.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.