XLOOKUP vs INDEX/MATCH: which lookup formula should you use?
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.
How an Excel Pro Uses XLOOKUP (R.I.P. VLOOKUP & INDEX MATCH)
Leila Gharani
Open on YouTube (opens in a new tab)
XLOOKUP and INDEX/MATCH do the same job — look up a value and return something from a related range — but they work differently, and the right choice depends on your Excel version and what the lookup needs to do.
What XLOOKUP replaced
Before XLOOKUP (introduced in Excel 365 and Excel 2021), the standard alternative to VLOOKUP was INDEX/MATCH:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))This combination works in any version of Excel, can look in any direction, and does not break when a column is inserted. XLOOKUP does everything INDEX/MATCH does, with a cleaner syntax.
Where XLOOKUP is simpler
Syntax. One function instead of two nested functions:
=XLOOKUP(D2, A:A, B:B)versus:
=INDEX(B:B, MATCH(D2, A:A, 0))Built-in if-not-found. XLOOKUP has an if_not_found argument that returns
a custom value instead of #N/A. With INDEX/MATCH you need IFERROR:
=IFERROR(INDEX(B:B, MATCH(D2, A:A, 0)), "Not found")Reverse search. Set search_mode to -1 to search from the last row
upward — useful when rows are added chronologically and you want the most
recent match. INDEX/MATCH needs a workaround for this.
Where INDEX/MATCH still wins
Compatibility. INDEX and MATCH work in Excel 2010, 2013, 2016, and 2019. XLOOKUP does not. If the workbook will be opened on an older version, or shared with someone who doesn’t have Microsoft 365, INDEX/MATCH is the safe choice.
Two-dimensional lookup. To look up by both row and column at once:
=INDEX(B2:D10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:D1, 0))XLOOKUP can approximate this by nesting one inside another, but INDEX/MATCH is more natural for two-way lookups.
Existing workbooks. INDEX/MATCH appears in a large proportion of professionally-built Excel models. You need to know it to maintain and understand inherited work, regardless of what you choose for new formulas.
Quick rule
For new formulas on Excel 365 or 2021: use XLOOKUP. For workbooks that need to open on older Excel versions, or for two-dimensional lookups: use INDEX/MATCH. Both are worth knowing — XLOOKUP for daily work, INDEX/MATCH for compatibility and older files.
Go deeper with this skill
Write formulas that are correct, readable, and resilient when the workbook changes. For this article, the goal is to practice: A practical comparison of XLOOKUP and INDEX/MATCH — what each can do, where they differ, and when the older formula is still the better choice.
Practice workbook setup
Create a small table with clean headers, sample edge cases, and expected outputs you can check manually.
Practice workflow
- Write the business rule in plain English before writing the formula.
- Build the formula in small pieces and test each piece.
- Use absolute references, table references, or named ranges intentionally.
- Test blanks, missing values, duplicate matches, and unexpected text.
Quality checks
- The formula returns the expected result for normal and edge-case rows.
- References point to stable ranges or tables.
- Someone else can understand the formula after reading the labels around it.
Common mistakes
- Fixing one row without testing the rest of the column.
- Using approximate match or partial criteria without meaning to.
- Letting hidden spaces or text numbers cause false mismatches.
Next actions
- Create three test rows that should return different outcomes.
- Compare the result with an alternate formula or manual filter.
Formula focus: test XLOOKUP(), INDEX(), MATCH() with normal rows, blank inputs, and at least one edge case.
