The XLOOKUP function in Excel
XLOOKUP finds a value in one range and returns the matching item from another. It is the modern replacement for VLOOKUP.
What XLOOKUP does
You point it at the cells to search and the cells to return. They can be anywhere, so it can look to the left, which VLOOKUP cannot. It matches exactly by default, and it has a built-in option for what to show when nothing is found. It is available in Microsoft 365, Excel 2021 and later, and Excel for the web.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
- lookup_value
- What to find.
- lookup_array
- The cells to search.
- return_array
- The cells to return from. It must be the same size as the lookup array.
- if_not_found
- Optional. What to show when there is no match.
Worked example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Look up product | ID | |
| 2 | 1001 | Notebook | 8 | Backpack | 1007 | |
| 3 | 1004 | Desk lamp | 25 | |||
| 4 | 1007 | Backpack | 40 | |||
| 5 | 1010 | Headphones | 60 |
The ID is to the left of the product name, and XLOOKUP still finds it.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Sales & Ops
Find an ID or a price from a name.
Finance
Match references across two lists.
Data cleanup
Check whether an entry exists in another list.
Common mistakes
- Using it in Excel 2019 or earlier, where it is not available.
- Search and return ranges of different sizes, which causes a #VALUE! error.
- Forgetting the not-found argument, so a missing value shows #N/A.
The XLOOKUP course
- 1Look up an ID by nameFree practice belowPractise
- 2Look left and rightComing soon
- 3Handle missing matchesComing soon
- 4Return a whole rowComing soon
- 5Search from the bottom upComing soon
- 6Replace VLOOKUP in old workbooksComing soon
Common questions
Is XLOOKUP better than VLOOKUP?
For most jobs, yes. It can look in any direction, it matches exactly by default, and it does not break when you insert columns.
Which versions of Excel have XLOOKUP?
Microsoft 365, Excel 2021 and later, and Excel for the web. It is not in Excel 2019 or earlier.