XLOOKUP nothing found

The XLOOKUP function searches an array or range, and returns the item(s) corresponding to the first match it finds.

Previously when using VLOOKUP a separate function like IFNA or IFERROR needed to be used to return a result if the lookup value wasn’t found. XLOOKUP builds this option directly into the function.

If the lookup_value doesn’t have a matching value in the lookup_array, a #N/A is returned.

There is an optional function variable [if_not_found] that can be used to return text, a cell reference or another function instead of the #N/A .

See the XLOOKUP function for more details on the function.

Syntax

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

Example

In the example, the XLOOKUP function is used to lookup two values, “Rent” and “Rental”.

“Rental” doesn’t exist as a value in the lookup_array.
Instead of a #N/A being returned, the text Nothing found is returned instead.

Tip

There is a trick to make the value returned stand out, without using conditional formatting.
First, make the return value text all upper case.
Then while editing the [if not found] parameter, press the Windows key and full stop (Win + .) and select an emoji.

=XLOOKUP(I12,B10:B22,C10:C22,"⚠️ NOTHING FOUND")

The missing value is easier to see when reviewing the results.