XLOOKUP with an 'If Not Found' Default

365 / 2021XLOOKUP

No more #N/A when a lookup misses. XLOOKUP’s built-in fourth argument lets you return a friendly message or a default value when there’s no match — no IFERROR wrapper needed.

Quick formula: look up A2, return “Not found” if it misses:

=XLOOKUP(A2, ids, names, "Not found")

The fourth argument is returned when nothing matches — cleaner than wrapping the whole formula in IFERROR.

Functions used (tap for the full reference guide):

XLOOKUP IFERROR

The example

A missing ID returns a message instead of an error.

	A	B
1	Lookup	Result
2	ID 102	Ann
3	ID 999	Not found

The formula

Default built right into the lookup:

=XLOOKUP(A2, ids, names, "Not found") // no IFERROR needed

How it works

XLOOKUP’s fourth argument handles the miss:

The first three arguments are the usual lookup value, lookup array, and return array.
The fourth — if_not_found — is returned when there’s no match: a message, a 0, or a blank "".
This replaces the old IFERROR(VLOOKUP(…), "…") pattern with one clean argument.
Unlike IFERROR, it only catches the “not found” case — real errors in the data still surface, which is usually what you want.

IFERROR still has a place. XLOOKUP’s default catches a missing lookup; if the returned cell itself contains an error, wrap with IFERROR too. For VLOOKUP (no built-in default), IFERROR(VLOOKUP(…), "Not found") remains the way.

Try it: interactive demo

Live demo

Look up an ID (try a missing one).

Result:

Variations

Default to zero

Numeric default:

=XLOOKUP(A2, ids, vals, 0)

Blank if missing

Empty string:

=XLOOKUP(A2, ids, names, "")

VLOOKUP equivalent

Wrap with IFERROR:

=IFERROR(VLOOKUP(A2, t, 2, FALSE), "Not found")

Pitfalls & errors

365/2021 only. XLOOKUP isn’t in Excel 2019 or earlier; use the IFERROR+VLOOKUP form there.

Default vs error. The fourth argument only fires on “no match,” not on errors inside the data — add IFERROR if the return cell can error.

Don’t skip the comma. The default is the 4th positional argument — make sure it’s in the right slot.

Practice workbook

📊

Download the free XLOOKUP with an 'If Not Found' Default practice workbook

XLOOKUP if-not-found examples (formula text + result) with zero, blank, and VLOOKUP variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I avoid #N/A with XLOOKUP?

Use the fourth argument: =XLOOKUP(value, lookup, return, "Not found"). It returns your default whenever there is no match — no IFERROR needed. Requires Excel 365/2021.

How is this different from IFERROR?

XLOOKUP's default only handles a missing lookup; IFERROR catches any error. If the returned cell itself can error, wrap with IFERROR as well.

What's the VLOOKUP equivalent?

VLOOKUP has no built-in default, so wrap it: =IFERROR(VLOOKUP(value, table, 2, FALSE), "Not found").

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: XLOOKUP · IFERROR

XLOOKUP with an ‘If Not Found’ Default