Excel IFERROR Function — Catch Errors Without Hiding Your Bugs (IFERROR vs IFNA)

TL;DR — IFERROR(formula, value_if_error) runs your formula and, if it errors, returns a fallback instead: =IFERROR(A2/B2, 0). The catch that costs people hours: IFERROR catches every error type — #REF!, #NAME?, #VALUE!, #DIV/0!, all of them — so it cheerfully hides the real bug (a deleted column, a misspelled function) along with the one you expected. For a lookup that might not find a match, use IFNA instead: it catches only #N/A and lets genuine errors surface. Better still, new lookups should use XLOOKUP's built-in if_not_found. Both IFERROR and IFNA need Excel 2013+.

=IFERROR(A2/B2, 0)                          ' B2 is 0 or blank -> 0 instead of #DIV/0!
=IFNA(XLOOKUP(A2, Ids, Names), "Not found")  ' only a no-match is caught

IFERROR is the function people reach for the instant they see an error in a cell — and that reflex is exactly the problem. Used well, it makes a model robust; used as a blanket, it turns your spreadsheet into one that looks clean while quietly lying to you. This guide is built around one strong recommendation: catch the error you expect, not every error there is.

What you'll learn

The mental model: IFERROR is a safety net, not a fix
The anti-pattern: wrapping everything in IFERROR hides real bugs
Why IFNA is usually the right tool for lookups
The performance trap of the old IF(ISERROR(…)) pattern
Choosing a fallback value that doesn't poison your totals
Why XLOOKUP's if_not_found often makes IFERROR unnecessary

The mental model: a safety net, not a fix

IFERROR doesn't prevent an error — it catches the formula after it has already failed and hands back something else:

' "Compute A2/B2. If that blows up, give me 0 instead."
=IFERROR(A2/B2, 0)

Think of it as a net strung under a tightrope. The net is sensible for a fall you can foresee — a divisor that's sometimes zero, a lookup that's sometimes missing. But a net doesn't know why you fell. If you fell because the rope was cut (you deleted a referenced column, or fat-fingered =VLOOKUP as =VLOOKP), the net catches you just the same — and now you never learn the rope is broken. That blindness is the whole danger.

The anti-pattern: IFERROR as a blanket

Here's the move that hides bugs. Something shows an error, so you wrap the entire formula and move on:

=IFERROR(SomeBigFormula(...), "")     ' make the red go away

The cell looks clean now. But IFERROR swallows all error types equally:

Error	What it usually means	Should you hide it?
`#N/A`	A lookup found no match	Often yes — it's expected
`#DIV/0!`	Divisor is zero or blank	Sometimes — if zero is valid
`#REF!`	A referenced cell was deleted	No — that's a real bug
`#NAME?`	Function/name misspelled	No — that's a real bug
`#VALUE!`	Wrong type (text where number expected)	No — usually a real bug

A blanket IFERROR treats the bottom three exactly like the top two. You delete a column three months later, the formula goes #REF!, and instead of a loud error you get a tidy blank — and a report that's silently wrong. The rule: never wrap a formula in IFERROR to make an error disappear. Wrap it only to handle a specific, expected failure — and prove the formula is correct on clean data first.

IFNA: catch only what you meant to catch

Most of the time, the error you actually expect is #N/A from a lookup that didn't find a match. That's exactly what IFNA is for — it catches #N/A and nothing else:

' IFERROR — hides a no-match AND a broken reference alike
=IFERROR(VLOOKUP(A2, Table, 2, 0), "Not found")

' IFNA — handles the no-match, lets #REF!/#NAME? still scream
=IFNA(VLOOKUP(A2, Table, 2, 0), "Not found")

Both give "Not found" when the id is missing. The difference shows up the day something else breaks: with IFNA, a deleted column still surfaces #REF! loud and clear; with IFERROR, it's buried under "Not found." For lookups — VLOOKUP, XLOOKUP, MATCH, INDEX/MATCH — IFNA is the correct default, not IFERROR. Reach for IFERROR only when the error you expect genuinely isn't #N/A (a deliberate divide that can hit zero, say).

The performance trap: don't compute the formula twice

Before IFERROR existed (pre-2007), the only way to trap an error was the IF(ISERROR(…)) pattern — and it has a hidden cost:

' Old pattern — runs the VLOOKUP TWICE (test, then again for the value)
=IF(ISERROR(VLOOKUP(A2, T, 2, 0)), "", VLOOKUP(A2, T, 2, 0))

' IFERROR — runs it once
=IFERROR(VLOOKUP(A2, T, 2, 0), "")

The old form evaluates the expensive lookup once to test it and a second time to return it — double the work on every row, which adds up fast on big sheets. IFERROR (and IFNA) evaluate the inner formula a single time. If you still have IF(ISERROR(…)) patterns in inherited workbooks, collapsing them to IFERROR/IFNA is a free speed-up and a readability win.

Choose a fallback that doesn't poison your numbers

The second argument's type matters more than people expect, because whatever you return flows into everything downstream:

Return 0 for a missing value and it gets averaged in — AVERAGE and charts now treat "no data" as a real zero and drag the result down.
Return "" (empty text) and any numeric formula on that cell can throw #VALUE!, and SUM will skip it but ISNUMBER reads FALSE.
For "no data," NA() is often the most honest fallback — charts skip #N/A points instead of plotting them as zero.

The judgment call: pick the fallback for how the cell is consumed, not just to clear the error. "Blank for display, NA() for charts, 0 only when zero is genuinely the right number" is a good default.

Why XLOOKUP often makes IFERROR unnecessary

For new work, the cleanest fix is to not wrap at all. XLOOKUP has a built-in fourth argument, if_not_found, that handles the missing case inside the lookup:

=XLOOKUP(A2, Ids, Names, "Not found")     ' no IFERROR/IFNA needed

This is strictly better than wrapping: it's evaluated once, it only triggers on an actual no-match (not on a #REF! elsewhere), and the intent is right there in the formula. The same idea shows up across modern functions — FILTER's if_empty argument plays the same role for "no rows matched." When the function offers its own not-found handler, prefer it over an outer IFERROR.

How ExcelMaster helps

The trouble with error handling is that the wrong approach looks identical to the right one — both make the red go away. ExcelMaster writes the version that doesn't lie: IFNA for lookups so real breaks still surface, XLOOKUP's if_not_found instead of an outer wrapper, and a fallback value chosen for how the cell feeds your totals and charts. You describe what "not found" should show; it makes sure a broken reference can't hide behind it.

Frequently asked questions

What's the difference between IFERROR and IFNA?

IFERROR catches every error type (#REF!, #NAME?, #VALUE!, #DIV/0!, #N/A, …). IFNA catches only #N/A. For lookups, IFNA is safer because it handles a no-match while still letting genuine bugs like #REF! show.

Why is wrapping everything in IFERROR a bad idea?

Because it hides real bugs. A deleted column (#REF!) or a misspelled function (#NAME?) gets silently replaced by your fallback, so a broken formula looks fine and your output is quietly wrong. Catch only the specific error you expect.

How do I hide a #DIV/0! error?

Wrap just that division and return a sensible value: =IFERROR(A2/B2, 0) or =IFERROR(A2/B2, ""). Make sure 0 is actually the right answer for "no divisor" — if the cell feeds an AVERAGE, a 0 will drag the mean down.

Should I use IFERROR with VLOOKUP or XLOOKUP?

With VLOOKUP, prefer IFNA over IFERROR so only a no-match is caught. With XLOOKUP, skip the wrapper entirely and use its fourth argument: =XLOOKUP(A2, Ids, Names, "Not found").

Does IFERROR work in older Excel versions?

IFERROR has shipped since Excel 2007 and IFNA since Excel 2013. The older IF(ISERROR(…)) workaround still works but evaluates your formula twice — switch to IFERROR/IFNA when you can.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-25.

Related guides: Excel IF · Excel IFS & SWITCH · Excel XLOOKUP · Excel FILTER