XLOOKUP in Excel: The Easier Way to Look Things Up
How XLOOKUP works in Excel 365 and Google Sheets: basic lookups, built-in not-found text, looking left, several columns and last-match search.
By Ayoub Farid · · 2 min read
XLOOKUP replaces VLOOKUP, HLOOKUP and most INDEX/MATCH formulas with one function that's easier to read. If your Excel has it (Microsoft 365, Excel 2021 or later, Excel for the web) or you use Google Sheets, it's the lookup to learn first.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])The first three are all you usually need:
- lookup_value: what you're looking for
- lookup_array: the column to search
- return_array: the column to bring back
Example
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-101 | Espresso | 2.50 |
| 3 | P-102 | Latte | 3.50 |
| 4 | P-103 | Tea | 2.00 |
=XLOOKUP(F2,A2:A4,C2:C4) → 3.50 for "P-102"No column number to count. Exact match is the default.
Built-in "not found"
=XLOOKUP(F2,A2:A4,C2:C4,"Not found")No IFERROR needed.
Look to the left
The return column can be anywhere:
=XLOOKUP("Latte",B2:B4,A2:A4) → P-102VLOOKUP can't do this.
Return several columns at once
=XLOOKUP(F2,A2:A4,B2:C4)Returns product and price, spilling into two cells.
Approximate matches (bands)
For tax brackets or grades, use match_mode -1 (exact or next smaller):
=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)Unlike VLOOKUP's TRUE, the table doesn't have to be sorted for this.
Find the last match
search_mode -1 searches from the bottom, e.g. the most recent price for a product:
=XLOOKUP(F2,A2:A500,C2:C500,,0,-1)Lock before filling down
Same rule as every lookup: lock the arrays with $ before you fill down.
=XLOOKUP(F2,$A$2:$A$500,$C$2:$C$500,"Not found")Compatibility
XLOOKUP isn't in Excel 2019 or earlier. If you send a file to someone on an older version, they'll see #NAME?. Read XLOOKUP vs VLOOKUP to choose.
Practise it
Lookups are a coming unit in Cellio. Our practice exercises include XLOOKUP tasks with answers.
Frequently asked questions
What is XLOOKUP in Excel?
A lookup function that searches one column and returns the matching value from another, in any direction, with exact match by default.
Which Excel versions have XLOOKUP?
Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. It's also in Google Sheets.
Is XLOOKUP better than VLOOKUP?
For most tasks, yes: no column numbers, looks left, exact match by default, built-in not-found value.
Can XLOOKUP return multiple values?
It can return several adjacent columns for one match. For all matching rows, use FILTER.