XLOOKUP if_not_found — the 4th argument that replaces #N/A.
XLOOKUP's 4th argument is the cleanest way to handle a missed lookup. Pass any value — a string, a number, a cell reference, or another formula — and XLOOKUP returns it whenever lookup_value isn't in lookup_array. No IFERROR wrapper, no IFNA, no nested helper columns. Below: an interactive demo, five battle-tested fallback patterns, and the failure modes if_not_found can't reach.
| A — ID | B — Product | |
|---|---|---|
| 2 | P001 | Wireless Mouse |
| 3 | P002 | USB-C Cable |
| 4 | P003 | Laptop Stand |
| 5 | P004 | Desk Lamp |
| 6 | P005 | Keyboard |
| 7 | XLOOKUP RESULT — "P999" not in column A | Not found |
| Goal | Without if_not_found | With if_not_found |
|---|---|---|
| Display a custom message | =IFNA(XLOOKUP(A2,ids,names), "Not found") | =XLOOKUP(A2, ids, names, "Not found") |
| Return zero for sums | =IFERROR(XLOOKUP(A2,ids,qty), 0) | =XLOOKUP(A2, ids, qty, 0) |
| Return blank (empty) | =IFNA(XLOOKUP(A2,ids,names), "") | =XLOOKUP(A2, ids, names, "") |
| Try a backup table | Nested IFERROR (ugly) | =XLOOKUP(A2, ids, names, XLOOKUP(A2, archive_ids, archive_names)) |
| Reference a parameter cell | =IFNA(XLOOKUP(…), $E$1) | =XLOOKUP(A2, ids, names, $E$1) |
What if_not_found does
One sentence: when no match exists in lookup_array, XLOOKUP returns this value instead of #N/A.
if_not_found is XLOOKUP's 4th positional argument and the single biggest reason to switch from VLOOKUP. It runs only on the “no match” path — every other XLOOKUP error (#VALUE! from mismatched array sizes, #REF! from deleted ranges) bubbles up unchanged.
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found)The fallback expression is evaluated lazily — only when there's no match. So a heavy formula in slot 4 only runs when the primary lookup misses, not on every successful row.
Pick the right fallback
The 4th argument accepts anything that resolves to a value. Five patterns cover 95% of real use.
Pattern 1 — Static string
For human-facing reports. Pick a phrasing your audience expects: "Not found", "—", "n/a", or a domain-specific term.
=XLOOKUP(A2, ids, names, "Not in catalog")Pattern 2 — Zero (or another number) for numeric sums
When the result feeds into SUM, AVERAGE, or arithmetic, an error breaks the chain. 0 lets the aggregate keep working — but only if “missing = zero” is honest for your data.
=XLOOKUP(A2, ids, quantity, 0) — safe for =SUM(B2:B100) downstream.Pattern 3 — Empty string for clean visuals
Dashboards often prefer empty cells over error chrome. "" leaves the cell visually blank while still letting ISBLANK-style checks recognize it as text.
=XLOOKUP(A2, ids, names, "")Pattern 4 — Nested XLOOKUP as a backup table
When you have two tables (live + archive, or main + override), nest a second XLOOKUP in the 4th slot. The lazy evaluation means the backup only fires on a primary miss.
=XLOOKUP(A2, live_ids, live_name, XLOOKUP(A2, archive_ids, archive_name, "Unknown"))Each level can have its own final fallback — here
"Unknown" fires only when neither table has the value.Pattern 5 — Cell reference for centralized control
Park the fallback in a single cell (say $E$1) and reference it from every XLOOKUP on the sheet. One edit retitles every missing row across hundreds of formulas.
=XLOOKUP(A2, ids, names, $E$1)Replacing IFERROR / IFNA wrappers
Most spreadsheets you inherit will have IFERROR wraps from the VLOOKUP era. if_not_found replaces them — usually for the better.
Side-by-side: same result, different structure, different performance characteristics, different bug surface.
=IFERROR(VLOOKUP(A2, table, 2, FALSE), "Not found")XLOOKUP equivalent
=XLOOKUP(A2, table_col1, table_col2, "Not found")Why the XLOOKUP version is usually better:
IFERRORcatches every error, including#REF!and#VALUE!that may signal a real bug.if_not_foundonly catches the no-match case — louder failure for unexpected problems.- VLOOKUP evaluates the whole lookup before
IFERRORdecides what to show. XLOOKUP returns directly. On large ranges, this is a noticeable speedup. - One function, one set of parentheses. The old pattern is two functions deep before you even read the lookup itself.
What if_not_found doesn't catch
if_not_found only fires on the “no match” path. Other XLOOKUP errors pass through unchanged.
A common gotcha: someone sets if_not_found to "Not found" and is surprised when the cell still shows #VALUE!. That's working as designed — different error, different cause.
| Error | Cause | Caught by if_not_found? |
|---|---|---|
#N/A | No match in lookup_array | ✅ Yes — replaced with fallback |
#VALUE! | lookup_array and return_array differ in size or direction | ❌ No — wrap in IFERROR if needed |
#REF! | Referenced range was deleted | ❌ No — fix the formula range |
#NAME? | Typo in “XLOOKUP” or running an old Excel without XLOOKUP support | ❌ No — install Excel 365/2021+ or check spelling |
#SPILL! | Return spans multiple cells but adjacent cells are occupied | ❌ No — clear the obstruction |
Excel & Sheets compatibility
XLOOKUP and its if_not_found argument are modern additions. The function and its 4th argument arrived together.
| Platform | XLOOKUP supported? | if_not_found supported? |
|---|---|---|
| Excel for Microsoft 365 (any plan) | ✅ Yes | ✅ Yes |
| Excel 2021 (one-time purchase) | ✅ Yes | ✅ Yes |
| Excel 2019 and older | ❌ No — use VLOOKUP + IFERROR | ❌ No |
| Excel for the web | ✅ Yes | ✅ Yes |
| Google Sheets | ✅ Yes (since Aug 2022) | ✅ Yes — identical semantics |
| LibreOffice Calc | ✅ Yes (since 7.3, Feb 2022) | ✅ Yes |
Related
The XLOOKUP main reference and adjacent error topics.
FAQ
6.01What does if_not_found do in XLOOKUP?▸
if_not_found is XLOOKUP's 4th (optional) argument. When lookup_value can't be found in lookup_array, XLOOKUP returns this fallback value instead of the default #N/A error. The fallback can be a string ("Not found"), a number (0), a cell reference (A1), or another formula — anything that resolves to a value.
6.02Can if_not_found return a formula instead of a string?▸
Yes. The 4th argument accepts any expression that returns a value. Common patterns: nest a second XLOOKUP to try a backup table when the primary misses — =XLOOKUP(A2, primary, primary_val, XLOOKUP(A2, fallback, fallback_val)). The inner XLOOKUP only fires when the outer one misses.
6.03Does if_not_found work in Google Sheets?▸
Yes. Google Sheets' XLOOKUP supports the same if_not_found argument with identical semantics. Both platforms return the fallback when lookup_value isn't in lookup_array, and both leave other errors (#REF!, #VALUE!, #NAME?) untouched.
6.04Why does my XLOOKUP still return #N/A even with if_not_found set?▸
Two possibilities. (1) You're using Excel 2019 or older, where XLOOKUP doesn't exist at all — the cell shows #NAME? rather than #N/A. (2) The #N/A is coming from elsewhere in your formula: e.g. a VLOOKUP nested inside the if_not_found slot can still return #N/A. if_not_found only catches the OUTER XLOOKUP's no-match case.
6.05What's the difference between if_not_found and IFNA / IFERROR?▸
if_not_found is XLOOKUP's native fallback — only triggers on a no-match (the same case that produces #N/A). IFNA wraps any expression and catches only #N/A regardless of source. IFERROR catches ALL errors — including #REF!, #VALUE!, #DIV/0! — which means it can silently hide unrelated bugs. Prefer if_not_found when you're already in an XLOOKUP, IFNA for narrow safety, and IFERROR only when you genuinely want to catch every error.
6.06Can I use a cell reference as the if_not_found fallback?▸
Yes. =XLOOKUP(A2, ids, names, $E$1) returns whatever is in E1 when no match is found. Useful for parameterizing the fallback message — change one cell to update every formula on the sheet.