Excel IFERROR Function

IFERROR checks a formula for an error and returns a value of your choosing instead of the default error code. If the formula runs fine, IFERROR just returns the normal result — nothing changes. The catch: it catches every error type, not just the one you're expecting, which means a typo can get silently hidden along with the error you meant to trap.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007. Not available in Excel 2003 or earlier.

In Excel 2003 and earlier, you'd combine IF and ISERROR to get the same result.

Syntax

=IFERROR(value, value_if_error)

Parameter	Required	Description
`value`	Yes	The formula or value Excel checks for an error. This is almost always another formula — a lookup, a division, a calculation.
`value_if_error`	Yes	What to return if `value` produces an error. Can be text, a number, a blank string (`""`), or another formula.

⚠

IFERROR catches every error type it encounters — #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL!, and #NUM! — with no way to filter by type. If you misspell a named range or a function name, Excel throws #NAME?, and IFERROR will happily swallow that too and show your fallback text instead. Your formula could be broken and you'd never know.

Basic Example

You're building a commission tracker. Column C has total sales per rep, column B has their deal count, and you're calculating average deal size. Reps with zero deals throw #DIV/0!.

=IFERROR(C2/B2, 0)

// C2  — total sales for this rep
// B2  — number of deals closed
// 0   — what to show instead of #DIV/0! when B2 is 0

Excel tries C2/B2 first. If the rep has closed at least one deal, it returns the average deal size like normal. If B2 is 0, dividing by zero throws #DIV/0!, and IFERROR intercepts it and returns 0 instead — so your dashboard shows a clean number instead of an error code.

How IFERROR Works

It evaluates the formula, checks for an error, then decides what to show

IFERROR runs value first. If the result is a normal value, that's what gets returned — value_if_error never even gets touched. Only when value produces one of Excel's error types does the second argument kick in. This is a single evaluation happening once, not a comparison of two separate calculations.

It catches every error type — that's the whole problem

#N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL!, #NUM! — IFERROR treats them all the same. This is what makes it fast to write and dangerous to over-rely on. A formula referencing a deleted column (#REF!) gets the same treatment as a lookup that simply didn't find a match (#N/A), even though one is a real bug and the other might be expected.

Blank cells are not errors

If value points to an empty cell, IFERROR treats it as an empty string (""), not an error. value_if_error never fires for a blank — you'll see nothing, not your fallback text. This trips people up when they expect IFERROR to catch missing data; it won't, because a blank isn't an error condition.

It spills across arrays and dynamic-array functions

If value is an array formula or a function like FILTER that returns multiple results, IFERROR applies to every cell the array spills into. In Excel 365, this works as a regular formula with no special entry required. In Excel 2019 and earlier, the same array behavior needs Ctrl+Shift+Enter to force it.

ℹ

A dynamic array is a formula that automatically fills, or "spills," results into multiple cells from a single formula entered in one cell.

Common Use Cases

Cleaning up VLOOKUP results

Your product table doesn't have every SKU a customer might type in. Instead of showing #N/A, show a message that makes sense to whoever reads the sheet.

=IFERROR(VLOOKUP(A2, products, 3, FALSE), "SKU not found")

Sequential lookups across two tables

You keep current-year prices in one table and legacy prices in an archive table. If the item isn't in the current table, check the archive before giving up.

=IFERROR(VLOOKUP(A2, current_prices, 2, FALSE), 
   IFERROR(VLOOKUP(A2, archive_prices, 2, FALSE), "Not found"))

// First VLOOKUP checks current_prices
// If that fails, nested IFERROR tries archive_prices
// If both fail, returns "Not found"

Nest as many of these as you have fallback tables. Each IFERROR only moves to the next lookup if the one before it fails — it doesn't run every lookup regardless.

Preventing #DIV/0! in a KPI dashboard

A conversion-rate column divides leads closed by leads received. Early in the month, some reps have zero leads received yet.

=IFERROR(closed/received, "—")   // shows a dash instead of #DIV/0!

Wrapping a FILTER formula that might return nothing

FILTER throws #CALC! when no rows match its condition — a common surprise for anyone new to dynamic arrays.

=IFERROR(FILTER(orders, region="West"), "No matching orders")

Without the wrapper, an empty result set breaks the spill and shows #CALC! instead of a clean message.

Handling Errors

IFERROR exists specifically to handle errors thrown by other formulas, so most of its use is intentional error trapping rather than error prevention on itself. The formulas most commonly wrapped in IFERROR are lookups, division, and dynamic-array functions — all of which throw predictable, catchable errors under normal conditions.

Common errors IFERROR is used to catch:

#N/A — from VLOOKUP, XLOOKUP, or MATCH when a lookup value isn't found
#DIV/0! — from any formula dividing by zero or an empty cell
#VALUE! — from math operations run against text instead of numbers
#REF! — from formulas referencing a cell, row, or column that's been deleted
#CALC! — from FILTER or other dynamic-array functions returning no results

=IFERROR(VLOOKUP(A2, employees, 3, FALSE), "Not found")
=IFERROR(D2/E2, "")   // blank string instead of #DIV/0!

✓

If you only want to catch #N/A and let every other error type surface normally, use IFNA instead of IFERROR. This stops a genuine bug — like a misspelled range or a broken cell reference — from getting hidden behind a friendly fallback message.

Notes & Gotchas

What is the IFERROR function and how does it work in Excel?

IFERROR is a logical function that runs a formula, checks whether the result is an error, and returns an alternate value if it is. If there's no error, it returns the original result unchanged. It replaces longer constructions like IF(ISERROR(formula), fallback, formula) with a single, shorter formula.

How do you use IFERROR with VLOOKUP in Excel?

Wrap the VLOOKUP formula as the first argument and put your fallback as the second: =IFERROR(VLOOKUP(A2, products, 3, FALSE), "Not found"). This catches the #N/A error VLOOKUP throws when the lookup value doesn't exist in the table. You can nest additional VLOOKUPs inside value_if_error to check a second or third table before giving up.

What is the difference between IFERROR and ISERROR or IFNA?

ISERROR just tests whether a value is an error and returns TRUE or FALSE — it doesn't fix anything on its own, so it's usually paired with IF. IFERROR does the testing and the fixing in one step, returning your custom value directly. IFNA works like IFERROR but only catches #N/A, letting every other error type through — use it when you specifically want other bugs to stay visible.

How do you nest an IFERROR function around a formula?

Put the formula you're checking as the first argument, exactly as you'd write it on its own: =IFERROR(A1/B1, 0). To chain fallbacks, nest a second IFERROR inside the first one's value_if_error argument, and keep going for as many fallback attempts as you need. Each layer only runs if every layer before it failed.

What error types does the IFERROR function catch?

IFERROR catches all seven of Excel's error types: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL!, and #NUM!. It doesn't distinguish between them — a genuine formula bug and an expected "no match" both get the same fallback treatment. That's exactly why it can mask real problems.

Why does IFERROR hide problems instead of fixing them?

Because it treats every error identically, a typo in a function name or a reference to a deleted column throws #NAME? or #REF! — and IFERROR replaces both with your fallback text just as readily as it would a genuine "value not found" case. If you've named a range sales_data and misspell it as sale_data inside a VLOOKUP, IFERROR will show "Not found" instead of the #NAME? error that would have pointed you straight to the typo. Test formulas without the IFERROR wrapper first, confirm they work, then add the wrapper last.

Can IFERROR run a calculation only when there's no error?

No — IFERROR only lets you specify what happens when there is an error. You can't tell it "if this isn't an error, do something different than just return it." For that, go back to IF(ISERROR(formula), fallback, some_other_calculation), which gives you full control over both branches.

Does IFERROR slow down large spreadsheets?

It can, on very large ranges, but not because of IFERROR itself. IFERROR evaluates value once — it doesn't recalculate the formula a second time to display it. The real cost comes from what you're wrapping: a volatile function like NOW() or OFFSET(), or a large array formula, forces Excel to recalculate on every change regardless of whether IFERROR is present. Spread across thousands of rows, that adds up. For simple divisions and lookups this is negligible; for volatile or array-heavy formulas over tens of thousands of rows, test performance before rolling it out sheet-wide.

Does IFERROR work with FILTER and other dynamic-array functions?

Yes. Wrapping FILTER in IFERROR catches the #CALC! error that appears when no rows match your condition, replacing a blank spill error with a message like "No results." In Excel 365, this behaves as a normal formula. In older versions that support arrays but not dynamic spilling, you may need to enter it as an array formula with Ctrl+Shift+Enter.

Related Functions

Function	Use this when...
`IF`	You need different results for both the error and non-error case, not just a fallback for errors.
`VLOOKUP`	You're building the lookup formula that IFERROR will most commonly wrap.
`IFNA` — You only want to catch #N/A and let other error types surface normally.