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.
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.
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.
| Capability | INDEX MATCH | XLOOKUP | Why it matters |
|---|---|---|---|
| Single-function syntax | ✗ Two functions | ✓ One function | INDEX needs MATCH to find the position; XLOOKUP does both. |
| Works in Excel 2016 / 2019 | ✓ All versions | ✗ Needs 2021 / 365 | XLOOKUP returns #NAME? on older Excel. |
| Excel 2021 / Microsoft 365 | ✓ Yes | ✓ Yes | Both fully supported on current Excel. |
| Google Sheets / LibreOffice | ✓ Yes | ✓ Yes | Both supported in current builds. |
| Look to the left of the key | ✓ Yes | ✓ Yes | Return range is independent of the lookup range in both. |
| Two-way (matrix) lookup | ✓ Native (2 MATCH) | ✓ Nested XLOOKUP | INDEX takes a row and column MATCH; XLOOKUP nests one inside another. |
| Built-in if-not-found | ✗ Wrap in IFERROR | ✓ 4th argument | INDEX MATCH propagates #N/A unless wrapped. |
| Approximate match on unsorted data | ✗ Needs sorting | ✓ match_mode −1 / 1 | MATCH type 1/−1 assumes a sorted range; XLOOKUP scans unsorted. |
| Search last-to-first | ✗ First only | ✓ search_mode −1 | MATCH always finds the first match. |
| Wildcard matching (* ?) | ✓ MATCH type 0 | ✓ match_mode 2 | Both support wildcards in text lookups. |
| Returns a usable cell reference | ✓ INDEX returns ref | ◐ Value / array | INDEX 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 arrays | Neither breaks when columns move, unlike VLOOKUP's index number. |
| Very large table scan speed | ◐ Often faster | ◐ Linear scan | MATCH can binary-search sorted data; XLOOKUP defaults to linear. |
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.
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.
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.
=INDEX(B2:B8,MATCH(103,A2:A8,0))=XLOOKUP(103,A2:A8,B2:B8)=INDEX(A2:A8,MATCH("Dave",B2:B8,0))=XLOOKUP("Dave",B2:B8,A2:A8)=INDEX(B2:B8,MATCH(999,A2:A8,0))=XLOOKUP(999,A2:A8,B2:B8,"Not found")=INDEX(B2:B8,MATCH(90000,D2:D8,1))=XLOOKUP(90000,D2:D8,B2:B8,,-1)=INDEX(B2:D4,MATCH("South",A2:A4,0),MATCH("Q2",B1:D1,0))=XLOOKUP("South",A2:A4,XLOOKUP("Q2",B1:D1,B2:D4))=INDEX(D2:D8,MATCH(1,(B2:B8="Carol")*(C2:C8="Eng"),0))=XLOOKUP(1,(B2:B8="Carol")*(C2:C8="Eng"),D2:D8)=INDEX(B2:B8,MATCH("Eng",C2:C8,0))=XLOOKUP("Eng",C2:C8,B2:B8,,0,-1)500 lookups against tables of increasing size, timed in this page's engine.
| Table size | INDEX MATCH | XLOOKUP |
|---|---|---|
| 1,000 rows × 500 lookups | 392.51 ms | 355.46 ms |
| 5,000 rows × 500 lookups | 4313.1 ms | 4330.56 ms |
| 10,000 rows × 500 lookups | 16848.83 ms | 20244.4 ms |
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.
| Situation | Use | Why |
|---|---|---|
| New work on Excel 2021 / 365, want simplicity | XLOOKUP | XLOOKUP is one function with fewer arguments to memorize. |
| Must support Excel 2016 / 2019 | INDEX MATCH | INDEX MATCH works in every version; XLOOKUP shows #NAME? there. |
| Two-way matrix lookup | Either | Both work — INDEX MATCH is native, XLOOKUP nests. Measured identical (130). |
| Need a built-in not-found message | XLOOKUP | XLOOKUP's 4th argument; INDEX MATCH needs an IFERROR wrapper. |
| Approximate match on unsorted data | XLOOKUP | XLOOKUP match_mode scans unsorted; MATCH needs the range sorted first. |
| Need the last matching row | XLOOKUP | XLOOKUP search_mode −1 is built in; MATCH finds only the first. |
| Need a reference for other formulas | INDEX MATCH | INDEX returns a true cell reference; XLOOKUP returns a value/array. |
| Scanning a very large table repeatedly | INDEX MATCH | INDEX MATCH is often faster than XLOOKUP's default linear scan. |
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.
| Task | INDEX MATCH / old way | XLOOKUP | What 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. |