XLOOKUP If Not Found: Show a Message Instead of #N/A

XLOOKUP if not found handling is built into the function: its fourth argument, if_not_found, tells Excel what to show when the value you are looking for isn't there. Instead of #N/A, you can show "Not found", a blank, a zero or the result of a second lookup. This guide covers each option, how it differs from IFERROR, and the small mistakes that stop it working.

XLOOKUP needs Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. The beginner's guide to XLOOKUP covers the basics if you haven't used it yet.

How the if_not_found argument works#

The if_not_found argument is the fourth of XLOOKUP's six arguments, and it is optional. Here is the full syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

If you leave it out and there is no match, XLOOKUP returns #N/A. If you fill it in, XLOOKUP returns your value instead. All the examples use this price list:

How to show a message instead of #N/A#

Put the message in double quotes as the fourth argument. With P-109 in F2:

=XLOOKUP(F2,A2:A6,C2:C6,"Not found")

The result is Not found, because P-109 isn't in column A. With P-103 in F2, the same formula returns 18.75 as normal.

Keep the message short and specific. "Code not in price list" tells the reader more than "Error". A consistent message also makes the failures easy to count. If the results are in G2:G100, =COUNTIF(G2:G100,"Not found") tells you how many codes need checking, and a conditional formatting rule on the same text can colour those rows so they stand out. You can also keep the message in a cell, such as H1, and point to it with $H$1. Then you change the wording in one place for every formula.

Return a blank, a zero or a cell instead#

The fourth argument accepts any value, not just text. Choose based on what happens to the result next:

Zero is the safest choice when the result feeds a SUM or another calculation. A "" result is text, so =G2*2 on it gives #VALUE!, although SUM simply skips it.

Use a second lookup as a backup#

The fourth argument can be another formula, so XLOOKUP can search a second list when the first one has no match. Say current products are in A2:A6 and discontinued products are in E2:E4 with prices in F2:F4:

=XLOOKUP(F2,A2:A6,C2:C6,XLOOKUP(F2,E2:E4,F2:F4,"Not found"))

Excel searches the current list first. Only if that fails does it try the discontinued list, and only if both fail does it show "Not found". This is cleaner than nesting IFERROR around two VLOOKUPs. You can chain a third list the same way, but beyond two or three lists it is usually better to stack the lists into one table, so there is only one place to search and update.

if_not_found vs IFERROR and IFNA#

if_not_found only replaces the #N/A that comes from a missing match. Other errors still show. That is a good thing, because those errors point to real problems you need to fix.

For example, if your lookup and return ranges are different sizes, XLOOKUP returns #VALUE!. if_not_found leaves that error visible, so you notice it. IFERROR would hide it behind your friendly message, and the sheet would look correct while being wrong. For older versions of Excel, the closest match is =IFNA(VLOOKUP(F2,A2:C6,3,FALSE),"Not found").

How to add a match mode without a message#

To use a match mode but keep the normal #N/A, leave the fourth argument empty with two commas. For example, =XLOOKUP(F2,A2:A6,C2:C6,,2) runs a wildcard search with no custom message. Count the commas carefully: the match mode must be the fifth argument. The guide to XLOOKUP wildcards and match modes explains each mode.

Common mistakes with XLOOKUP if not found#

These are the reasons your message doesn't appear, or appears when it shouldn't:

1. Missing quotes around text. =XLOOKUP(F2,A2:A6,C2:C6,Not found) gives #NAME?. Write "Not found".
2. Expecting the message for an empty result. If the code is found but its price cell is empty, XLOOKUP returns 0, not your message. The value was found; there was just nothing next to it.
3. Hidden spaces or text numbers. If F2 holds "P-103 " with a trailing space, XLOOKUP shows your message even though P-103 is in the list. A message that appears too often is a sign of dirty data, as the guide to fixing XLOOKUP when it's not working explains.
4. A blank lookup cell. If F2 is empty, you may get your message on every empty row. Wrap the formula to skip blanks: =IF(F2="","",XLOOKUP(F2,A2:A6,C2:C6,"Not found")).

When the value is on a different tab, the fourth argument works the same way; see XLOOKUP from another sheet or workbook. The XLOOKUP function guide is a quick reference for all six arguments.

Practise it#

Handling missing values cleanly is a small detail that job tests like to check. Practise XLOOKUP in a live spreadsheet: you type the formula, including the if_not_found message, and it checks your answer instantly. Then practise XLOOKUP match modes to see how the fourth and fifth arguments work together.