Function comparison · engine-measured

INDEX MATCH vs XLOOKUP

Two ways to look up a value VLOOKUP can't reach: the battle-tested INDEX MATCH combo and the modern XLOOKUP. They agree on exact and left lookups, then diverge on approximate-match sorting, search direction, and ergonomics. Below, every difference is measured on identical data in a live engine, not copied from a feature list. Edit the cells and watch both formulas re-evaluate side by side.

fx
INDEX MATCH(…)  vs  XLOOKUP(…)
7
differences measured live
2→1
functions: combo vs single
2021+
Excel needed for XLOOKUP
360°
lookup direction (both look left)
▶ Try both live See the feature matrix
🏆Verdict: XLOOKUP for new work on Excel 2021/365; INDEX MATCH for compatibility and references

XLOOKUP is the simpler, more capable default on modern Excel: one function, built-in not-found handling, left lookup, unsorted approximate match and last-to-first search. INDEX MATCH remains the right choice when you need Excel 2016/2019 compatibility, a true cell reference, or the fastest scan on very large tables. They return identical results on exact and left lookups; the differences are ergonomics, sorting requirements and search direction.

INDEX MATCH strengths

  • Works in every Excel version, including 2016 and 2019
  • INDEX returns a true cell reference other formulas can use
  • Two-way matrix lookup is native: INDEX(range, MATCH(row), MATCH(col))
  • Often faster on very large tables (binary-capable MATCH)

XLOOKUP strengths

  • One function instead of two — fewer arguments to memorize
  • Built-in if_not_found (4th argument), no IFERROR wrapper
  • Approximate match on unsorted data via match_mode −1 / 1
  • Last-to-first search built in via search_mode −1

Feature-by-feature matrix

Fourteen capabilities, scored honestly. ✓ full support, ◐ partial / with caveats, ✗ not supported. Both look left and do two-way lookups; the split is compatibility, approximate-match sorting, search direction and reference returns.

CapabilityINDEX MATCHXLOOKUPWhy it matters
Single-function syntax✗ Two functions✓ One functionINDEX needs MATCH to find the position; XLOOKUP does both.
Works in Excel 2016 / 2019✓ All versions✗ Needs 2021 / 365XLOOKUP returns #NAME? on older Excel.
Excel 2021 / Microsoft 365✓ Yes✓ YesBoth fully supported on current Excel.
Google Sheets / LibreOffice✓ Yes✓ YesBoth supported in current builds.
Look to the left of the key✓ Yes✓ YesReturn range is independent of the lookup range in both.
Two-way (matrix) lookup✓ Native (2 MATCH)✓ Nested XLOOKUPINDEX takes a row and column MATCH; XLOOKUP nests one inside another.
Built-in if-not-found✗ Wrap in IFERROR✓ 4th argumentINDEX MATCH propagates #N/A unless wrapped.
Approximate match on unsorted data✗ Needs sorting✓ match_mode −1 / 1MATCH type 1/−1 assumes a sorted range; XLOOKUP scans unsorted.
Search last-to-first✗ First only✓ search_mode −1MATCH always finds the first match.
Wildcard matching (* ?)✓ MATCH type 0✓ match_mode 2Both support wildcards in text lookups.
Returns a usable cell reference✓ INDEX returns ref◐ Value / arrayINDEX can feed a reference into other range-based formulas.
Multiple criteria (boolean array)✓ MATCH(1,(c1)*(c2),0)✓ XLOOKUP(1,(c1)*(c2),r)Both use boolean-array multiplication; legacy INDEX MATCH needs CSE.
Insert / delete columns safely✓ Independent ranges✓ Independent arraysNeither breaks when columns move, unlike VLOOKUP's index number.
Very large table scan speed◐ Often faster◐ Linear scanMATCH can binary-search sorted data; XLOOKUP defaults to linear.

Syntax, side by side

INDEX MATCH composes two functions — MATCH finds the position, INDEX returns the value. XLOOKUP folds both into one call and adds optional if_not_found, match_mode and search_mode arguments.

INDEX MATCH

INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
return_rangeThe column (or row) to pull the answer from.
MATCH(...)Finds the position of lookup_value within lookup_range.
lookup_valueWhat you are searching for.
lookup_rangeThe column to search.
0Exact match. Use 1 (ascending) or −1 (descending) for approximate.
=INDEX(B2:B8, MATCH(103, A2:A8, 0))

XLOOKUP

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_valueWhat you are searching for.
lookup_arrayThe range to search.
return_arrayThe range to return from (any direction).
if_not_foundOptional. Value returned when no match exists.
match_modeOptional. 0 exact, −1 exact-or-next-smaller, 1 exact-or-next-larger, 2 wildcard.
search_modeOptional. 1 first-to-last, −1 last-to-first, 2 / −2 binary.
=XLOOKUP(103, A2:A8, B2:B8)

Live head-to-head playground

One editable table, two formulas. Pick a scenario or type your own — both sides re-evaluate in the same engine, so you see exactly where they diverge.

A1Sample data — click any cell to edit; both formulas re-evaluate live

INDEX MATCH

—

XLOOKUP

—
HyperFormula 3.5 · live

Every difference, measured

These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.

Exact lookup — the baseline tie

Find the Name for ID 103. INDEX needs MATCH to locate the row; XLOOKUP does both in one call.
INDEX MATCH
=INDEX(B2:B8,MATCH(103,A2:A8,0))
Carol
XLOOKUP
=XLOOKUP(103,A2:A8,B2:B8)
Carol
Both return Carol. INDEX(MATCH()) is two functions; XLOOKUP is one. Same result, different ergonomics.

Looking left — both can, VLOOKUP can't

Find the ID for Name Dave. The return column (ID) sits left of the lookup column (Name).
INDEX MATCH
=INDEX(A2:A8,MATCH("Dave",B2:B8,0))
104
XLOOKUP
=XLOOKUP("Dave",B2:B8,A2:A8)
104
Both return 104. Unlike VLOOKUP, INDEX MATCH and XLOOKUP both look left freely — the return range is independent of the lookup range.

No match — built-in default vs IFERROR wrap

Look up ID 999, which does not exist.
INDEX MATCH
=INDEX(B2:B8,MATCH(999,A2:A8,0))
#N/A
XLOOKUP
=XLOOKUP(999,A2:A8,B2:B8,"Not found")
Not found
INDEX MATCH propagates #N/A from MATCH; you wrap it in IFERROR to customize. XLOOKUP has a built-in if_not_found argument.

Approximate match — sorting requirement

Find the Name for the largest Salary <= 90000. XLOOKUP match_mode -1 scans unsorted data; MATCH type 1 needs ascending sort.
INDEX MATCH
=INDEX(B2:B8,MATCH(90000,D2:D8,1))
Frank
XLOOKUP
=XLOOKUP(90000,D2:D8,B2:B8,,-1)
Eve
XLOOKUP(-1) correctly returns Eve (88000) on unsorted data. MATCH(,1) assumes ascending sort and returns a wrong row here — sort first or use XLOOKUP.

Two-way matrix lookup

Find the Q2 value for the South region in a cross-tab. INDEX takes a row MATCH and a column MATCH; XLOOKUP nests.
INDEX MATCH
=INDEX(B2:D4,MATCH("South",A2:A4,0),MATCH("Q2",B1:D1,0))
130
XLOOKUP
=XLOOKUP("South",A2:A4,XLOOKUP("Q2",B1:D1,B2:D4))
130
Both return 130. INDEX MATCH handles two-way lookup natively with two MATCH calls; XLOOKUP nests one XLOOKUP inside another.

Multiple criteria via boolean arrays

Find the Salary where Name is Carol AND Dept is Eng. Both use a (cond1)*(cond2) array matched against 1.
INDEX MATCH
=INDEX(D2:D8,MATCH(1,(B2:B8="Carol")*(C2:C8="Eng"),0))
105000
XLOOKUP
=XLOOKUP(1,(B2:B8="Carol")*(C2:C8="Eng"),D2:D8)
105000
Both return 105000. Multi-criteria lookup works in both via boolean-array multiplication; in older Excel INDEX MATCH needs Ctrl+Shift+Enter.

Last match — search_mode vs first-only MATCH

Find the last Name in Dept Eng. XLOOKUP search_mode -1 searches bottom-up; MATCH only finds the first.
INDEX MATCH
=INDEX(B2:B8,MATCH("Eng",C2:C8,0))
Alice
XLOOKUP
=XLOOKUP("Eng",C2:C8,B2:B8,,0,-1)
Frank
MATCH returns the first Eng (Alice); XLOOKUP(search_mode -1) returns the last Eng (Frank). Last-match is a built-in XLOOKUP mode.

Engine benchmark

500 lookups against tables of increasing size, timed in this page's engine.

Table sizeINDEX MATCHXLOOKUP
1,000 rows × 500 lookups392.51 ms355.46 ms
5,000 rows × 500 lookups4313.1 ms4330.56 ms
10,000 rows × 500 lookups16848.83 ms20244.4 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

A decision rule for the situations that actually come up. Short version: XLOOKUP for new work on modern Excel; INDEX MATCH for older-version compatibility, references, or large-table speed.

SituationUseWhy
New work on Excel 2021 / 365, want simplicityXLOOKUPXLOOKUP is one function with fewer arguments to memorize.
Must support Excel 2016 / 2019INDEX MATCHINDEX MATCH works in every version; XLOOKUP shows #NAME? there.
Two-way matrix lookupEitherBoth work — INDEX MATCH is native, XLOOKUP nests. Measured identical (130).
Need a built-in not-found messageXLOOKUPXLOOKUP's 4th argument; INDEX MATCH needs an IFERROR wrapper.
Approximate match on unsorted dataXLOOKUPXLOOKUP match_mode scans unsorted; MATCH needs the range sorted first.
Need the last matching rowXLOOKUPXLOOKUP search_mode −1 is built in; MATCH finds only the first.
Need a reference for other formulasINDEX MATCHINDEX returns a true cell reference; XLOOKUP returns a value/array.
Scanning a very large table repeatedlyINDEX MATCHINDEX MATCH is often faster than XLOOKUP's default linear scan.

INDEX MATCH → XLOOKUP migration

Six translations. The one rule that fixes most: MATCH's range becomes lookup_array, INDEX's range becomes return_array, and any IFERROR wrapper becomes the 4th argument.

TaskINDEX MATCH / old wayXLOOKUPWhat changed
Exact lookup=INDEX(B2:B8,MATCH(103,A2:A8,0))=XLOOKUP(103,A2:A8,B2:B8)Drop the INDEX wrapper; MATCH's range becomes lookup_array, INDEX's range becomes return_array.
Left lookup=INDEX(A2:A8,MATCH("Dave",B2:B8,0))=XLOOKUP("Dave",B2:B8,A2:A8)Same ranges, one function.
With IFERROR=IFERROR(INDEX(B2:B8,MATCH(999,A2:A8,0)),"n/a")=XLOOKUP(999,A2:A8,B2:B8,"n/a")The IFERROR wrapper becomes the 4th argument.
Two-way matrix=INDEX(B2:D4,MATCH("South",A2:A4,0),MATCH("Q2",B1:D1,0))=XLOOKUP("South",A2:A4,XLOOKUP("Q2",B1:D1,B2:D4))The column MATCH becomes an inner XLOOKUP returning the column.
Multi-criteria=INDEX(D2:D8,MATCH(1,(B2:B8="Carol")*(C2:C8="Eng"),0))=XLOOKUP(1,(B2:B8="Carol")*(C2:C8="Eng"),D2:D8)The boolean array moves straight into XLOOKUP's lookup_array.
Last match=INDEX(B2:B8,MATCH("Eng",C2:C8,0))=XLOOKUP("Eng",C2:C8,B2:B8,,0,-1)Add search_mode −1; INDEX MATCH can only find the first.

Frequently asked questions

Is XLOOKUP faster than INDEX MATCH?
Not necessarily. XLOOKUP's default search is a linear scan; INDEX MATCH can use binary search on sorted data and is often faster on very large tables. Our relative HyperFormula benchmark shows them in the same order of magnitude. For typical sheets the difference is negligible — choose on compatibility and ergonomics, not speed.
Can INDEX MATCH look to the left?
Yes. Because INDEX's return range and MATCH's lookup range are independent, you can return a column to the left of the lookup column — something VLOOKUP cannot do. XLOOKUP shares this ability; both measured 104 when looking up Dave's ID.
Why does my INDEX MATCH approximate match return the wrong row?
MATCH with type 1 (or −1) assumes the lookup range is sorted ascending (or descending). On unsorted data it returns an arbitrary wrong position — in our test it returned Frank instead of Eve. XLOOKUP's match_mode −1 / 1 scans unsorted data correctly. Either sort the data first or use XLOOKUP.
How do I do a two-way lookup with each?
INDEX MATCH: =INDEX(matrix, MATCH(row_key, row_headers, 0), MATCH(col_key, col_headers, 0)). XLOOKUP: nest one XLOOKUP inside another — the inner XLOOKUP returns the matching column, the outer returns the matching row. Both measured 130 on the South/Q2 cell.
Does XLOOKUP replace INDEX MATCH completely?
On Excel 2021/365 it covers almost everything more simply. But INDEX MATCH still wins on older Excel compatibility, returning a true cell reference, and some large-table performance cases. Many pros keep both in their toolkit.
Can both handle multiple criteria?
Yes. Both use a boolean array: MATCH(1,(cond1)*(cond2),0) inside INDEX, or XLOOKUP(1,(cond1)*(cond2),return_range). Both measured 105000 for Carol AND Eng. In legacy Excel the INDEX MATCH form must be entered with Ctrl+Shift+Enter; XLOOKUP handles arrays natively.
Which should I learn first?
Learn INDEX MATCH first — it works everywhere and teaches you how lookups actually compose. Then add XLOOKUP for cleaner modern formulas. Understanding MATCH's position logic also makes XMATCH, SORT and FILTER easier to grasp.