How to Handle Missing Results in XLOOKUP in Excel

Replace #N/A with a clear message, a safe zero or a second lookup using XLOOKUP’s built-in if_not_found argument.

You will learn to

• Show a message instead of #N/A
• Return 0 when not found so calculations keep working
• Fall back to a second table when the first has no match

How it works

When XLOOKUP cannot find the lookup value it returns #N/A. Its fourth argument, if_not_found, replaces that error with whatever you choose: =XLOOKUP(H1,A2:A9,B2:B9,"Invalid code") shows a friendly message, while a 0 keeps later arithmetic working.

if_not_found can even be another formula. =XLOOKUP(code,A2:A9,B2:B9,XLOOKUP(code,D2:D6,E2:E6,"Not recognised")) checks a second table only when the first has no match. It is cleaner than wrapping the lookup in IFERROR, which would also hide genuine mistakes such as a wrongly sized range.

=XLOOKUP(H1,A2:A9,B2:B9,"Invalid code")