VLOOKUP vs XLOOKUP vs INDEX MATCH: which should you use?
By the Analistable team · Updated · 2 min read
Use XLOOKUP if everyone opening the file has Excel 365, Excel 2021 or Google Sheets — it looks left, defaults to exact match and handles missing values. Use INDEX MATCH when the file must work in older Excel versions. Use VLOOKUP only for simple lookups in legacy files.
Part of our guide: How to join spreadsheets on a common column
Side-by-side comparison
We compared the three functions on the questions that actually decide which one to use in a shared workbook.
| VLOOKUP | INDEX MATCH | XLOOKUP | |
|---|---|---|---|
| Looks to the left | No | Yes | Yes |
| Default match type | Approximate | Set by you (0 = exact) | Exact |
| Survives inserted columns | No | Yes | Yes |
| Built-in 'not found' value | No (wrap in IFNA) | No (wrap in IFNA) | Yes |
| Returns several columns at once | No | No | Yes |
| Excel 2019 and older | Yes | Yes | No |
| Google Sheets | Yes | Yes | Yes |
VLOOKUP: the classic, with sharp edges
=VLOOKUP(A2, B:D, 3, FALSE) searches the first column of B:D and returns the third column. It works everywhere, but it can only look to the right, defaults to approximate match if you forget FALSE, and breaks when someone inserts a column because the index number is hard-coded.
INDEX MATCH: flexible and backward compatible
=INDEX(D:D, MATCH(A2, B:B, 0)) finds the row with MATCH and returns the value with INDEX. It can look left, survives inserted columns, and works in every Excel version — at the cost of a longer formula.
XLOOKUP: the modern default
=XLOOKUP(A2, B:B, D:D, "Not found") does what INDEX MATCH does in one function, with exact match by default and a built-in fallback for missing values. It can also search from the bottom and return several columns at once.
- Available in Excel 365, Excel 2021 and later, Excel for the web, and Google Sheets.
- Not available in Excel 2019 or earlier — recipients see #NAME?.
When a lookup isn't enough
All three functions return a single match. In the example below, every formula pulls 120 for Bakery Lune — the first order — although the customer actually spent 200 across two orders.
With real data the gap grows: if one customer has twenty orders, you still get only the first. For totals per customer, matches across several sheets, or data split between Google Sheets and Excel, a join is the right tool — see our guide to joining two spreadsheets on a common column.
| Approach | Formula | Result |
|---|---|---|
| VLOOKUP | =VLOOKUP("C-101", Orders!B:C, 2, FALSE) | 120 |
| INDEX MATCH | =INDEX(Orders!C:C, MATCH("C-101", Orders!B:B, 0)) | 120 |
| XLOOKUP | =XLOOKUP("C-101", Orders!B:B, Orders!C:C) | 120 |
| SUMIFS (aggregate) | =SUMIFS(Orders!C:C, Orders!B:B, "C-101") | 200 |
| Join + group by | Customers ⟕ Orders, SUM(amount) | 200 |
Frequently asked questions
- Is XLOOKUP faster than VLOOKUP?
- For typical sheets the difference is negligible. Choose based on compatibility and readability rather than speed.
- Does XLOOKUP work in Google Sheets?
- Yes, Google Sheets supports XLOOKUP with the same arguments as Excel.
- Can these functions return multiple matches?
- No — each returns the first match. Use FILTER, a pivot table or a join when you need every matching row.