VLOOKUP vs XLOOKUP: which should you use?
XLOOKUP is VLOOKUP's modern replacement — but VLOOKUP still wins on one thing: it runs everywhere, including old Excel versions and every LibreOffice release. Here's the practical difference, with compatibility verified by actually executing both.
The differences at a glance
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Look left of the key column | No — the key must be the first column | Yes — lookup and return ranges are independent |
| Default match mode | Approximate (a classic silent-error trap — always pass FALSE) | Exact (the safe default) |
| Built-in if-not-found | No — wrap in IFERROR | Yes — 4th argument |
| Column referenced by | Number (breaks when columns are inserted) | Range (survives column inserts) |
| Search from the end | No | Yes (search mode -1) |
| Return multiple columns at once | No | Yes — return range can be several columns (spills) |
| Works in old Excel (2019 and earlier) | Yes | No — Excel 2021+ / 365 only |
| LibreOffice support (executed result) | All versions | 24.8+ only — returns #NAME? in 24.2 |
Which should you use?
- VLOOKUP — The file must open correctly for users on old Excel or LibreOffice 24.2, or you're maintaining an existing sheet that already uses it (with FALSE as the 4th argument).
- XLOOKUP — Everyone's on Excel 2021+/365, current Google Sheets, or LibreOffice 24.8+ — exact-match default, left-lookups, and the built-in not-found argument remove VLOOKUP's three classic bugs.
Compatibility (from executed tests)
We executed both in real LibreOffice: VLOOKUP works in every release we test; XLOOKUP first works in 24.8 (verified #NAME? in 24.2). In Google Sheets both are available.
Example formulas
| VLOOKUP (exact match — always pass FALSE) | =VLOOKUP("cherry",A2:B10,2,FALSE) |
| XLOOKUP equivalent with a not-found default | =XLOOKUP("cherry",A2:A10,B2:B10,"Not found") |
Full per-version details on each function page: VLOOKUP · XLOOKUP.