The XLOOKUP function
Finds a value in one range and returns the matching item from another. Exact match by default, with an optional value for not found.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value
- The value to find.
- lookup_array
- The range to search.
- return_array
- The range to return from, the same size as lookup_array.
- if_not_found (optional)
- What to return when there is no match, instead of #N/A.
- match_mode (optional)
- 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcards.
- search_mode (optional)
- 1 first to last (default), -1 last to first.
Examples
D1
=XLOOKUP(1"Plum",2A2:A4,3B2:B4)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 5 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: Find 1Plum in 2the fruit in A2:A4 and return 3its price from B2:B4.
D1=XLOOKUP("Kiwi",A2:A4,B2:B4,"Not found")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | Not found | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
Practice XLOOKUP
Read the guide
- Excel for job interviews: what gets tested and how to prepare
- VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use
- Excel for finance and accounting: the skills you actually use
- Google Sheets vs Excel: what is the same, what is different, and how to switch
- How to create a drop-down list in Excel, and make it update itself
Questions
Which Excel versions have XLOOKUP?
Excel for Microsoft 365, Excel 2021 and later, Excel for the web and Google Sheets. Excel 2019 and older do not.