XLOOKUP Not Found and IFERROR in Excel with Examples
Fix XLOOKUP not-found results without hiding broken formulas. Compare if_not_found, IFNA and IFERROR with copyable examples and an Excel 2016/2019 alternative.
#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.
Quick answer
Use XLOOKUP's if_not_found argument or IFNA when a missing match is expected. Use IFERROR only when every possible error from the wrapped expression should share the same fallback. For zero denominators and other known conditions, test the condition directly with IF; it documents the reason and leaves unrelated errors visible.
| Situation | Recommended pattern | Why |
|---|---|---|
| Lookup may have no match | XLOOKUP(...,"Not found") |
Handles only the expected miss |
| VLOOKUP/INDEX-MATCH may miss | IFNA(formula,"Not found") |
Keeps #REF! and #VALUE! visible |
| Denominator may be zero | IF(B2=0,"",A2/B2) |
Tests the real condition |
| Any failure truly means the same thing | IFERROR(formula,fallback) |
Appropriate broad catch |
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.
A lookup example you can check
On a sheet named Prices, enter A100 and 12 in A2:B2, then A200 and 0 in A3:B3. On a separate sheet, enter a product key in A2 and this formula in B2:
=XLOOKUP(A2, Prices!$A$2:$A$3, Prices!$B$2:$B$3, "NOT LISTED")
| Key in A2 | Expected result in B2 | Meaning |
|---|---|---|
| A100 | 12 | Found a price |
| A200 | 0 | Found a genuine zero |
| A999 | NOT LISTED | No matching product key |
These are synthetic test rows, not customer results. A missing match is different from a match whose source price is empty: the latter can return zero. Check source completeness and duplicate keys before trusting the output. XLOOKUP returns the first match by default.
Excel 2016 and 2019: XLOOKUP is unavailable. For this same two-column table, use the following exact-match alternative:
=IFNA(VLOOKUP(A2, Prices!$A$2:$B$3, 2, FALSE), "NOT LISTED")
IFNA also catches #N/A propagated from the matched source cell; it does not prove the key was missing. If that distinction matters, inspect the source and keep a separate match-status column. See the blank-cell filling example for preserving existing values.
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. Imagine a report with 40 zeros: three are real zeros and 37 conceal references to a deleted sheet. The report looks complete but is not.
IFERROR(..., "") feeding math. SUM ignores text, including formula-generated empty strings in referenced cells, so a total can look valid while omitting failed calculations. Direct arithmetic with that text may instead return #VALUE!. Keep a separate error count rather than treating either result as proof that the source is complete.
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:
- Count what's being caught. In a helper column, repeat the inner formula without the wrapper. If those diagnostic results occupy E2:E20, count them with
=SUMPRODUCT(--ISERROR(E2:E20))in a cell outside that range. - 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.
More reliable formula patterns
Return a status instead of silently returning zero:
=IF(B2=0, "CHECK DENOMINATOR", A2/B2)
Keep a missing match visible as an error, while checking source blanks separately:
=XLOOKUP(A2, Prices!A:A, Prices!B:B, NA())
Validate the key before looking it up:
=IF(TRIM(A2)="", "MISSING KEY", XLOOKUP(TRIM(A2), Prices!A:A, Prices!B:B, "NOT LISTED"))
These visible states are easier to count, filter, and investigate than empty strings. If a clean presentation is required, keep the diagnostic formula in a helper column and present a separate user-facing result.
Official references
- Microsoft: IFNA function
- Microsoft: XLOOKUP function and version availability
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.
