Catch Errors with IFERROR

All versionsIFERRORIFNA

A single missing value or stray divide-by-zero can splatter #N/A and #DIV/0! across a whole report. IFERROR wraps a formula and swaps any error for a clean fallback — a blank, a zero, or a friendly message.

Quick formula: to show a blank instead of an error from a lookup:

=IFERROR(VLOOKUP(E2, A:B, 2, 0), "")

If the formula works, you get its result; if it errors, you get the fallback — here, an empty string.

Functions used (tap for the full reference guide):

IFERROR IFNA

The example

Dividing units by hours — one row has zero hours, which would error.

	A	B	C
1	Units	Hours	Per hour
2	120	8	15.0
3	90	0	n/a
4	75	5	15.0

The formula

Guarding the division:

=IFERROR(A2/B2, "n/a") // 90/0 would be #DIV/0! → shows n/a

How it works

IFERROR runs the formula and only steps in on failure:

IFERROR first evaluates A2/B2.
If it returns a normal value (15.0), that’s what you see — IFERROR is invisible.
If it returns any error (#DIV/0!, #N/A, #VALUE!…), IFERROR returns the second argument instead — "n/a".
Use "" for a blank, 0 to keep totals working, or text to explain the gap.

Don’t hide errors you should see. IFERROR catches every error type, so a genuine #REF! from a broken formula gets silently swallowed too. When you only want to handle “not found,” use IFNA — it catches just #N/A and lets real bugs surface.

Try it: interactive demo

Live demo

Change the hours; see how IFERROR handles a divide-by-zero.

Units (A2)

Hours (B2)

Fallback

Shows:

Variations

Catch only "not found" with IFNA

Lets real errors through, hides only #N/A:

=IFNA(VLOOKUP(E2, A:B, 2, 0), "Not found")

Fallback to a second lookup

Try one source, then another:

=IFERROR(VLOOKUP(E2, A:B, 2, 0), VLOOKUP(E2, X:Y, 2, 0))

Keep sums working with 0

So an error doesn’t break a downstream SUM:

=IFERROR(A2/B2, 0)

Pitfalls & errors

IFERROR masks all errors. A typo that throws #NAME? looks “handled” and you never notice. Use the narrowest catch (IFNA) when you can.

It recalculates the formula twice in the fallback pattern. =IFERROR(big(), big()) runs big() again on error; on heavy formulas that’s slow. Modern XLOOKUP has a built-in if_not_found that avoids this.

IFERROR is Excel 2007+. In Excel 2003 use =IF(ISERROR(x), fallback, x).

Practice workbook

📊

Download the free Catch Errors with IFERROR practice workbook

The division example with live IFERROR, plus IFNA and the fallback-lookup patterns, and 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I get rid of #DIV/0! and #N/A errors in Excel?

Wrap the formula in IFERROR with a fallback: =IFERROR(A2/B2, "n/a") or =IFERROR(VLOOKUP(...), ""). The fallback shows whenever the formula would error.

What's the difference between IFERROR and IFNA?

IFERROR catches every error type; IFNA catches only #N/A. Use IFNA for lookups so genuine errors like #REF! or #NAME? still surface as bugs.

Should I use IFERROR around VLOOKUP or XLOOKUP?

XLOOKUP has a built-in if_not_found argument, so prefer that. For VLOOKUP, IFERROR or IFNA is the standard way to handle missing values.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: IFERROR · IFNA