Handling Misses With if_not_found

What happens when XLOOKUP cannot find the value you asked for? By default it returns the #N/A error.

That error is technically correct but looks ugly in a report and can break any formula that uses the result. XLOOKUP gives you a clean, built-in way to handle this.

XLOOKUP has an optional fourth argument called if_not_found. Whatever you put there is returned when no match exists.

This is a major upgrade over VLOOKUP, which needed to be wrapped in IFERROR. With XLOOKUP the fallback is part of the same function.

The most common use is showing readable text instead of #N/A.

Here, if the product in D2 is not in the list, the cell shows "Not found" rather than an error code. Anyone reading the sheet instantly understands what happened.

Sometimes a number is more useful than text, especially if the result feeds into a calculation.

You can return 0 when there is no match, so a later SUM or multiplication still works without producing its own error.

To leave the cell visually empty when nothing matches, return an empty text string with two quotation marks.

This is handy for clean dashboards where blank cells are preferable to placeholder text. Note the cell is not truly empty - it contains an empty string - which matters if other formulas test for blanks.

Suppose a sales rep types an order ID into D2 to look up the customer name. Order IDs are in column A, names in column C.

If they mistype the ID, the formula returns a clear instruction instead of a confusing error. The fallback message guides them to fix their input.

The if_not_found value does not have to be typed text. It can point to another cell.

For instance, you might store a default region in G1 and return that whenever a specific lookup fails. This keeps the fallback editable without touching the formula.

You could still wrap XLOOKUP in IFERROR, but there is a subtle difference.

• if_not_found catches only the no-match case
• IFERROR hides every error, including ones caused by a genuine mistake in your formula

Using if_not_found is safer because real bugs stay visible instead of being masked.

A neat trick: make the fallback of one XLOOKUP be another XLOOKUP.

Search a primary list first; if the item is missing, search a backup list. This is far cleaner than nested IFERRORs and reads almost like plain English.

When a lookup result feeds into math, a numeric fallback keeps everything working.

Here a missing price returns 0, so the multiplication by quantity in E2 still produces a number rather than an error spreading across your sheet.

Pick a fallback that matches the cell's purpose:

• Text like "Not found" for human-readable reports
• 0 when the value is added or multiplied later
• An empty string for clean visual layouts
• Another lookup for multi-source data

The fourth argument turns brittle lookups into polished, professional results.

Test your understanding of the if_not_found argument.

You learned to handle lookups that find nothing:

• XLOOKUP returns #N/A by default when there is no match
• The optional if_not_found argument supplies a clean fallback
• Use text, 0, a blank, a cell, or even a second XLOOKUP
• It only catches no-match cases, keeping real errors visible

Next you will search left and from the bottom of a list.