IFERROR + XLOOKUP: Building Error-Proof Excel Formulas

#N/A scattered across a report looks broken, so the reflex is to wrap everything in IFERROR and move on. Sometimes that's right. Often it buries a real problem — a broken lookup, a divide-by-zero, a typo in a key — under a tidy blank cell.

This guide covers the three tools for handling formula errors, the difference between them, and the patterns that hide expected errors while letting unexpected ones stay visible.

The three tools

IFERROR catches every error type:

=IFERROR(A2/B2, 0)

If the division produces #DIV/0!, #VALUE!, #REF! — anything — you get 0. That breadth is also its danger: a #REF! from a deleted column deserves attention, not a silent 0.

IFNA catches only #N/A:

=IFNA(VLOOKUP(A2, Prices!A:B, 2, FALSE), "not listed")

This is almost always what you want around lookups: "value not found" is an expected condition, while #VALUE! or #REF! from the same formula still signals a genuine bug.

XLOOKUP's built-in if_not_found makes the fallback part of the lookup itself:

=XLOOKUP(A2, Prices!A:A, Prices!B:B, "not listed")

Cleaner than wrapping, and it only handles the not-found case — other errors still surface.

Rule of thumb: handle expected, expose unexpected

Ask one question per formula: which error is normal here?

• A lookup that can legitimately miss → handle #N/A (IFNA or XLOOKUP's fallback).
• A ratio whose denominator can legitimately be zero → test the denominator explicitly:

=IF(B2=0, "", A2/B2)

Testing the condition is better than catching the error: IF(B2=0,…) documents why the fallback exists, while IFERROR around the same division would also swallow a #VALUE! caused by text in column B.

• Everything else → let it error. A visible #REF! costs you a minute; an invisible one costs you a wrong report.

The classic mistakes

Blanket IFERROR over a whole column. You ship a report with 40 zeros, three of which are real zeros and 37 of which are a renamed sheet nobody noticed.

IFERROR(..., "") feeding math. An empty string in a numeric column turns downstream SUMs subtly wrong and produces #VALUE! in arithmetic — the error you hid comes back two columns later, further from its cause.

Catching errors that indicate dirty data. If VALUE(A2) errors because the column mixes text and numbers, the fix is cleaning the column, not catching the symptom.

Auditing a sheet full of hidden errors

Inheriting a workbook where every formula is wrapped in IFERROR? Two moves:

1. Count what's being caught. In a helper column, repeat the inner formula without the wrapper and count errors with =SUM(--ISERROR(...)) entered over the range.
2. Ask an assistant to audit it. AI for Excel can scan a sheet's formulas, list which cells are currently suppressing errors and what type each error is, and distinguish "lookup miss, handled correctly" from "broken reference, silently hidden" — then fix the ones you approve, with an automatic backup before any change.

This kind of formula audit is tedious by hand and fast for a tool that reads the workbook programmatically. If you'd rather describe the goal in a sentence than build helper columns, try the add-in free — and for the plain-English route to writing these formulas in the first place, see AI formula generation.

FAQ

What is the difference between IFERROR and IFNA?

IFERROR catches every error type; IFNA catches only #N/A (the "not found" error). Around lookups, prefer IFNA or XLOOKUP's if_not_found argument so that structural errors like #REF! stay visible.

Should I use IFERROR everywhere?

No. Handle only the errors you expect (usually lookup misses and zero denominators) and let unexpected errors show. A visible error is diagnostic information; a hidden one is a future incorrect report.

How do I find all cells with errors in Excel?

Press F5 → Special → Formulas → check only Errors. Excel selects every error cell in the sheet. An AI assistant can go further and categorize them by error type and cause.