Concept
Availability: Excel 365 and Excel 2021 or later (also Excel for the web). In Excel 2019 and older, XLOOKUP shows #NAME? — use INDEX+MATCH there.
Same product sheet (A1:E6) as the previous lessons.
1. Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Only the first three are required: what to find, where to find it, what to return.
=XLOOKUP("P103", A2:A6, D2:D6) → 4500
Exact match is the default — no FALSE needed.
2. Left lookup — just swap the columns
=XLOOKUP("Desk", B2:B6, A2:A6) → P104
3. Built-in "not found"
=XLOOKUP(H1, A2:A6, D2:D6, "Code not found")
No IFNA needed.
4. Return several columns at once
=XLOOKUP(H1, A2:A6, B2:E6)
With P102 in H1, this fills four cells in a row: Mobile, Electronics, 18000, 40. This is called a spill. Keep the cells to the right empty, or you'll get #SPILL!.
5. Last match (search_mode)
Imagine an order log where one product appears many times, newest at the bottom. VLOOKUP gives only the first (oldest) one. XLOOKUP can search bottom-up:
=XLOOKUP("Mobile", B2:B100, E2:E100, "None", 0, -1)
0 = exact match, -1 = search from last to first.
6. match_mode options
| Value | Meaning |
|---|---|
0 |
exact match (default) |
-1 |
exact, or next smaller item |
1 |
exact, or next larger item |
2 |
wildcard match (*, ?) |
Wildcard example — first product starting with "Pri":
=XLOOKUP("Pri*", B2:B6, D2:D6, "None", 2) → 9500
Options -1 and 1 are covered in Lesson 4.
7. XMATCH — MATCH upgraded
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
=XMATCH("Chair", B2:B6) → 3
Differences from MATCH: exact match is the default (no 0 needed), and it supports reverse search and wildcards like XLOOKUP. Use it with INDEX for two-way lookups:
=INDEX(A2:E6, XMATCH(H1, A2:A6), XMATCH(H2, A1:E1))
8. VLOOKUP vs INDEX+MATCH vs XLOOKUP
| VLOOKUP | INDEX+MATCH | XLOOKUP | |
|---|---|---|---|
| Left lookup | ❌ | ✅ | ✅ |
| Safe when columns are inserted | ❌ | ✅ | ✅ |
| Default match | approximate ⚠️ | approximate ⚠️ | exact ✅ |
| Built-in not-found | ❌ | ❌ | ✅ |
| Last match | ❌ | complicated | ✅ |
| Works in old Excel | ✅ | ✅ | ❌ |
Rule of thumb: on Excel 365/2021, use XLOOKUP. If the file will be shared with people on older versions, use INDEX+MATCH.
Common mistakes
lookup_array and return_array of different sizes (#VALUE!). Blocked spill range (#SPILL!). Sending XLOOKUP files to someone on Excel 2019 or older.