VLOOKUP vs INDEX/MATCH: which is better?
INDEX/MATCH was the power user's lookup long before XLOOKUP existed, and it still matters because it runs in every spreadsheet app and every version — while fixing VLOOKUP's worst habits.
The differences at a glance
| VLOOKUP | INDEX | MATCH | |
|---|---|---|---|
| Look left of the key column | No | Yes — MATCH and INDEX ranges are independent | |
| Survives inserting/deleting columns | No — the column number goes stale | Yes — ranges adjust automatically | |
| Readability | One compact call | Two nested calls — harder for beginners | |
| Speed on large data | Scans the whole table width | Can be faster — only touches two columns | |
| Compatibility | Universal | Universal — works in every version of all three apps |
Which should you use?
- VLOOKUP — Quick lookups in simple, stable tables where the key is already the first column and nobody inserts columns.
- INDEX/MATCH — The sheet must work for users on ANY version (including LibreOffice 24.2 and old Excel where XLOOKUP doesn't exist), you need a left-lookup, or the table's columns change over time.
Compatibility (from executed tests)
All three functions execute correctly in every LibreOffice release we test, all Excel versions, and Google Sheets — INDEX/MATCH is the most portable robust lookup that exists. If everyone's on a modern version, XLOOKUP does everything INDEX/MATCH does with one call.
Example formulas
| VLOOKUP (key must be column 1) | =VLOOKUP("cherry",A2:B10,2,FALSE) |
| INDEX/MATCH equivalent (works even if B is left of A) | =INDEX(B2:B10,MATCH("cherry",A2:A10,0)) |
Full per-version details on each function page: VLOOKUP · INDEX · MATCH.