How does IFERROR differ from wrapping a formula in IF(ISERROR(...),...,...)?

IFERROR is a concise, single-function alternative that automatically catches all error types, whereas IF(ISERROR(...)) requires an explicit error check and can be more verbose. Both return the same result, but IFERROR is easier to read and maintain.

Can IFERROR be used with array formulas that spill?

Yes. When an array formula generates a spill error (#SPILL!), IFERROR can replace the entire spill with a single fallback value. However, the fallback will not spill; it will occupy a single cell.

What happens if value_if_error itself evaluates to an error?

IFERROR does not evaluate value_if_error when the primary expression succeeds. If the primary expression errors, IFERROR returns value_if_error as-is, even if that value is another error code.

Should I use IFERROR for data validation before calculations?

Generally, use IFERROR to handle unexpected errors after validation, not as a substitute for proper data checks. Functions like ISNUMBER, ISDATE, or explicit IF tests provide clearer intent when validating inputs.

IFERROR function

IFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.

=IFERROR(value, value_if_error)

Generate a IFERROR formula

Describe what you need. The generator will reach for IFERROR where IFERROR is the right tool, and tell you when it is not.

Arguments

How IFERROR reads its arguments

Argument	Required	Description
value	Required	The expression or cell reference to evaluate; can be any type that might produce a spreadsheet error.
value_if_error	Required	The fallback value returned when the first argument evaluates to an error; must be a constant, cell reference, or expression.

Returns

A single scalar value of the same type as the first argument, or the alternative value if an error occurs.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Lookup an employee salary safely

EmployeeID	Name	Department	HireDate	Salary	Status
101	Alice Johnson	Marketing	2015-06-12	72000	Active
102	Bob Smith	Sales	2018-03-04	68000	Active
104	Dana Lee	HR	2020-11-19	59000	On Leave

=IFERROR(VLOOKUP(103,HR!A:F,5,FALSE),"Not found")

Result: Not found

The VLOOKUP searches column A for the ID 103. Because the dataset does not contain that ID, VLOOKUP returns #N/A. IFERROR catches the #N/A and substitutes the text "Not found" as the final output.

2. Calculate years of service for a named employee

EmployeeID	Name	Department	HireDate	Salary	Status
101	Alice Johnson	Marketing	2015-06-12	72000	Active
102	Bob Smith	Sales	2018-03-04	68000	Active

=IFERROR(YEAR(TODAY())-YEAR(INDEX(HR!D:D,MATCH("Alice Johnson",HR!B:B,0))),0)

Result: 9

MATCH finds Alice Johnson in column B (row 2). INDEX returns the corresponding HireDate (June 12, 2015). YEAR(TODAY()) minus YEAR(HireDate) yields 9 years (assuming the current year is 2024). If the name were missing or the date malformed, the inner expression would error and IFERROR would return 0.

3. Provide a default when a division error occurs

EmployeeID	Name	Department	HireDate	Salary	Status
101	Alice Johnson	Marketing	2015-06-12	72000	Active
102	Bob Smith	Sales	2018-03-04	68000	Active

=IFERROR(INDEX(HR!E:E,MATCH("Sales",HR!C:C,0))/0,"N/A")

Result: N/A

MATCH locates the first row where Department equals "Sales" (row 2). INDEX retrieves the Salary (68000). Dividing that salary by zero produces the #DIV/0! error. IFERROR intercepts the error and returns the string "N/A" instead.

Common errors

Which IFERROR error are you seeing?

Error	Why it happens	How to fix it
#DIV/0!	A division operation in the primary expression attempts to divide by zero, which IFERROR can trap and replace.	Ensure the divisor is never zero, or wrap the division in IFERROR as shown to supply a fallback.
#N/A	A lookup function such as VLOOKUP or MATCH cannot find the requested key, producing #N/A which IFERROR can handle.	Verify the lookup key exists in the source range, or use IFERROR to provide a default like "Not found".
#VALUE!	An operation receives an argument of the wrong type, for example trying to subtract a text string from a number, causing #VALUE! that IFERROR can replace.	Clean the data so that numeric operations only involve numbers, or wrap the expression in IFERROR to return a sensible alternative.

Tips and when to use something else

Use IFERROR when you want a single catch-all for any error type; it simplifies formulas that would otherwise need multiple nested IF statements.
If you only need to handle missing lookup results (#N/A), consider IFNA, which is shorter and more explicit.
Place IFERROR as the outermost wrapper; inner functions should remain uncluttered so you can still see the original error during debugging.
Remember that IFERROR also catches errors generated by array formulas, so it works with dynamic array functions like FILTER or UNIQUE.

Related functions

IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.IFNAIFNA returns the result of a formula unless it evaluates to #N/A, in which case it returns a specified alternate value.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.SWITCHSWITCH evaluates an expression and returns the matching result value, or a default if provided, simplifying multi-branch logic in a single formula.

Frequently asked questions

How does IFERROR differ from wrapping a formula in IF(ISERROR(...),...,...)?: IFERROR is a concise, single-function alternative that automatically catches all error types, whereas IF(ISERROR(...)) requires an explicit error check and can be more verbose. Both return the same result, but IFERROR is easier to read and maintain.
Can IFERROR be used with array formulas that spill?: Yes. When an array formula generates a spill error (#SPILL!), IFERROR can replace the entire spill with a single fallback value. However, the fallback will not spill; it will occupy a single cell.
What happens if value_if_error itself evaluates to an error?: IFERROR does not evaluate value_if_error when the primary expression succeeds. If the primary expression errors, IFERROR returns value_if_error as-is, even if that value is another error code.
Should I use IFERROR for data validation before calculations?: Generally, use IFERROR to handle unexpected errors after validation, not as a substitute for proper data checks. Functions like ISNUMBER, ISDATE, or explicit IF tests provide clearer intent when validating inputs.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator