XLOOKUP vs INDEX/MATCH: is there still a reason to nest?
XLOOKUP does in one call nearly everything INDEX/MATCH does in two — so the real question is portability: INDEX/MATCH works in every spreadsheet ever shipped; XLOOKUP needs modern versions (LibreOffice 24.8+, verified by execution).
The differences at a glance
| XLOOKUP | INDEX | MATCH | |
|---|---|---|---|
| Left lookups | Yes | Yes | |
| Built-in if-not-found | Yes — 4th argument | No — wrap in IFERROR | |
| Two-dimensional lookup (row AND column) | Nest two XLOOKUPs | Classic INDEX(range,MATCH(),MATCH()) — arguably cleaner | |
| Search from end / binary search | Built-in modes | MATCH is ascending-only (approximate) | |
| Return several columns at once | Yes (spills) | INDEX with 0 as row/col can, awkwardly | |
| Old Excel (2019 and earlier) | No | Yes | |
| LibreOffice (executed result) | 24.8+ (#NAME? in 24.2) | Every version we test |
Which should you use?
- XLOOKUP — Modern-version environments — simpler to read, safer defaults, built-in not-found handling.
- INDEX/MATCH — Maximum portability (old Excel, LibreOffice 24.2, unknown recipients), or two-dimensional lookups where INDEX with two MATCHes reads naturally.
Compatibility (from executed tests)
Executed results: INDEX and MATCH work in every LibreOffice release we test; XLOOKUP first works in LibreOffice 24.8 and returns #NAME? in 24.2. All are available in current Google Sheets and Excel 365; only INDEX/MATCH exists in Excel 2019 and earlier.
Example formulas
| XLOOKUP | =XLOOKUP("cherry",A2:A10,B2:B10,"Not found") |
| INDEX/MATCH equivalent | =IFERROR(INDEX(B2:B10,MATCH("cherry",A2:A10,0)),"Not found") |
| Two-dimensional INDEX/MATCH/MATCH | =INDEX(B2:E10,MATCH("cherry",A2:A10,0),MATCH("Q3",B1:E1,0)) |
Full per-version details on each function page: XLOOKUP · INDEX · MATCH.