XLOOKUP vs INDEX/MATCH: Which One Should You Actually Use?
XLOOKUP is newer and easier to read, but INDEX/MATCH still wins in a few specific situations. Here's how to choose.
Both XLOOKUP and INDEX/MATCH solve the same core problem: find a value in one column and return a matching value from another. XLOOKUP, introduced in Excel 2019/365, was built to replace VLOOKUP's biggest annoyances. But INDEX/MATCH hasn't disappeared, and for good reason.
The basic syntax
=XLOOKUP(A2, Names, Salary, "Not found")XLOOKUP: lookup value, lookup array, return array, optional fallback.
=INDEX(Salary, MATCH(A2, Names, 0))INDEX/MATCH: MATCH finds the position, INDEX returns the value at that position.
XLOOKUP wins on readability immediately — one function, named arguments, and a built-in fallback value instead of wrapping everything in IFERROR. For anyone newer to Excel, this alone is worth switching.
Try it yourself
Look up a department by employee ID
| ID | Name | Department | Salary |
|---|---|---|---|
| A-101 | Priya Nair | Finance | 82,000 |
| A-102 | Marcus Lee | Operations | 71,000 |
| A-103 | Sofia Reyes | Marketing | 68,000 |
| A-104 | Tom Becker | Finance | 95,000 |
| A-105 | Aisha Khan | Engineering | 104,000 |
Formula
=XLOOKUP("A-104", A2:A6, C2:C6, "Not found")Where XLOOKUP is strictly better
- Built-in "not found" handling — no need to wrap in IFERROR()
- Searches left-to-right or right-to-left — VLOOKUP can only look right
- Returns an array, so it can pull multiple columns at once without repeating the formula
- Defaults to exact match, so you won't accidentally get approximate matches like old VLOOKUP
Where INDEX/MATCH still matters
If your file needs to work for anyone on an older, non-Microsoft-365 version of Excel (XLOOKUP isn't available in Excel 2016/2019 perpetual licenses), INDEX/MATCH is the only portable option between the two. It also tends to be the more familiar pattern in older workbooks you might inherit, so being able to read it is still a useful skill even if you don't write new formulas with it.
A side-by-side example
Say you have an employee list with Name, Department, and Salary, and you want to look up a salary by name, falling back to a clear message if the name isn't found.
=XLOOKUP(F2, A:A, C:C, "No match")The modern way — one function handles the whole thing.
=IFERROR(INDEX(C:C, MATCH(F2, A:A, 0)), "No match")The compatible way — same result, needs IFERROR to catch the error.
The bottom line: if everyone opening the file has Excel 365 or Excel 2021+, default to XLOOKUP. If the file travels to people on older licenses, or you're maintaining an existing INDEX/MATCH-heavy workbook, stick with what's already there rather than mixing both styles in the same sheet.
Want the full worked example?
Automate Your Reports: Power Query for Beginners
A complete, practical ebook covering the Folder connector, query folding, modular queries, and a full monthly-report walkthrough.
Get the eBook — ₹199