Excel XLOOKUP function: the easier lookup
XLOOKUP looks for a value in one range and returns the matching value from another. =XLOOKUP(E3,J2:J4,K2:K4) finds Luis's region, West, and returns its manager. It does an exact match by default and can look left or right.
The lookup examples use this small table in J1:L4, next to the sales data:
| J Region | K Manager | L Rate | |
|---|---|---|---|
| 2 | East | Ann Cole | 5% |
| 3 | West | Ben Ortiz | 4% |
| 4 | North | Cara Lim | 6% |
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Most of the time you only need the first three, plus if_not_found.
Examples
| Formula | Result |
|---|---|
=XLOOKUP(E3,J2:J4,K2:K4) | Ben Ortiz |
=XLOOKUP("North",J2:J4,K2:K4) | Cara Lim |
=XLOOKUP("South",J2:J4,K2:K4,"Not found") | Not found |
XLOOKUP vs VLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Default match | Approximate, unless you add FALSE | Exact |
| Look left | No | Yes |
| Column number | Counted by hand | Not needed |
| Not-found message | Needs IFERROR | Built in |
| Works in Excel 2019 and older | Yes | No |
Which versions have XLOOKUP
Microsoft 365, Excel 2021, Excel 2024, Excel for the web and Google Sheets. Excel 2019, 2016 and older don't have it. If someone opens your file in those versions, they'll see #NAME?.
Common mistakes
- Ranges of different sizes. The lookup range and return range must have the same number of rows.
- Using it on a test built for Excel 2016, like many Kenexa Prove It versions.
Try it: XLOOKUP the manager
In C3, use XLOOKUP to show the manager for Luis's region (E3).
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Does Google Sheets have XLOOKUP?
Yes. It works the same way as in Excel.
Is XLOOKUP better than INDEX and MATCH?
It's easier to read and does the same job in most cases. INDEX and MATCH still matter for older Excel versions.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How VLOOKUP works in Excel, why you should always end it with FALSE, how to lock the table with $, its limits, and a live exercise.
How INDEX and MATCH work together in Excel, why analysts use it instead of VLOOKUP, the 0 that makes it exact, and a live exercise.
How to use IFERROR in Excel to replace #N/A, #DIV/0! and other errors, when not to use it, and a live exercise with VLOOKUP.