XLOOKUP

What it does

XLOOKUP is the modern lookup. You give it the range to search and the range to return separately, so it can look in any direction, and it matches exactly unless you ask for something else. It's in Excel 2021 and later, Excel for Microsoft 365 and Excel for the web.

Syntax

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

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ;

Arguments

lookup_value

What to look for.

lookup_array

The range to search.

return_array

The range to return a value from, the same size as the search range.

if_not_found

What to show when there's no match. Without it you get #N/A.

match_mode

0 (the default) for an exact match. Other values accept the next smaller or larger item, or wildcards.

search_mode

1 (the default) searches from the first item, -1 from the last, which finds the latest entry.

Example

What's Kai's email address?

The formula in E2:

=XLOOKUP(D2;A2:A4;B2:B4;"not found")

Result: kai@example.com

Kai is the third name in A2:A4, so XLOOKUP returns the third item of B2:B4. If the name were missing, the fourth argument would show "not found".

Common mistakes

• Search and return ranges of different sizes give a #VALUE! error.
• Sharing the file with someone on Excel 2019 or older, where XLOOKUP doesn't exist and shows #NAME?.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• XLOOKUP: the modern lookupModule 5 · Lookups
• #N/A: nothing foundModule 13 · Finding and fixing errors
• Checking invoicesModule 14 · Combined skills

More from this module

• IFNA
• VLOOKUP
• HLOOKUP

All functions