The XLOOKUP function
The lookup of Excel 2021 and Microsoft 365: a column to search, a column to return, and what to show if nothing is found. It does everything VLOOKUP does, without its pitfalls.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
In a French Excel: =RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé; [si_non_trouvé]; [mode_correspondance]; [mode_recherche]).
Arguments
- lookup_value: what you are looking for.
- lookup_array: the column (or row) to search.
- return_array: the column to return, of the same size; it can be to the left.
- if_not_found: what to return instead of #N/A.
- match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 with wildcards.
- search_mode: 1 from the start (default), -1 from the end (the last occurrence).
An example
=XLOOKUP(A2,Customers!A:A,Customers!C:C,"Unknown")
Looks for A2 in column A of Customers and returns column C of the same row, or "Unknown" if it is not there.
Pitfalls
- Missing from Excel 2019 and earlier: the file opened in an older version shows #NAME?.
- Two ranges of different sizes:
A2:A100andC2:C90return #VALUE!. - Spaces and numbers stored as text: like any lookup, it only finds what is written identically.
Going further
- VLOOKUP, the old way.
- INDEX and MATCH, for versions without XLOOKUP.
- Find the right formula: the lookup written for you, in either version.
Frequently asked questions
- Does my Excel have XLOOKUP?
- Excel 2021, Excel 2024 and Microsoft 365 do, on Windows, Mac and the web. Excel 2019 and earlier do not.