For years INDEX MATCH was the "advanced" alternative to VLOOKUP. Then XLOOKUP arrived. Here is the honest comparison.
INDEX MATCH is actually two functions nested together. MATCH finds the position of a value in a range; INDEX returns the value at that position from a different range.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
=INDEX(C2:C100, MATCH("London", A2:A100, 0))
-- Find "London" in column A, return the value in column C at that rowWhy did people prefer it over VLOOKUP? Because it can look left (not just right), the column doesn't need to be a fixed index number, and it handles inserted or deleted columns gracefully.
XLOOKUP was designed to replace both VLOOKUP and INDEX MATCH with a single, cleaner function.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP("London", A2:A100, C2:C100, "Not found")
-- Same result as the INDEX MATCH above, but in one function| Feature | INDEX MATCH | XLOOKUP |
|---|---|---|
| Look left of the lookup column | ✓ | ✓ |
| Handle errors gracefully | Needs IFERROR wrapper | Built-in if_not_found |
| Return multiple columns at once | Needs two MATCH calls | ✓ Single formula |
| Exact match by default | ✓ (with 0) | ✓ Default |
| Wildcard matching | ✓ | ✓ |
| Search last to first | Harder | search_mode = -1 |
| Works in Excel 2019 and older | ✓ | ✗ (365/2021 only) |
| Readable to non-experts | Confusing | Much cleaner |
Use XLOOKUP if you have Excel 365 or 2021. It is simpler to write, simpler to read, handles errors built-in, and returns multiple columns without extra formulas. The only reason to use INDEX MATCH today is compatibility — if your workbook will be opened by someone on Excel 2019 or earlier, XLOOKUP will break for them. In that case, INDEX MATCH remains the best option. If everyone on your team uses 365, switch to XLOOKUP and don't look back.
Even if you use XLOOKUP daily, understanding INDEX MATCH makes you a better Excel user — it teaches you to think in terms of position-finding and value-returning separately, which helps with more advanced array formulas.
ExcelPro has hands-on exercises for every formula on this page — type it yourself, get instant feedback.
Start practising free →