Three generations of Excel lookup functions. Here's which one wins in 2026 — and when the others still matter.
=VLOOKUP(lookup_value, table_range, col_index, [match_type])
=VLOOKUP(A2, $D$2:$F$100, 2, 0)
-- Find A2 in column D, return column ELimitations: Can only look right. Breaks when columns are inserted. Column index is fragile. Slower on large data. Returns first match only.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
=INDEX(C2:C100, MATCH(A2, B2:B100, 0))
-- Can look in any direction, handles column insertionsAdvantages over VLOOKUP: Looks left or right. Stable when columns are inserted. Faster on large datasets. Works in all Excel versions.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP(A2, B2:B100, C2:C100, "Not found")
-- Cleaner syntax, built-in error handling, any directionAdvantages over both: Simplest syntax. Built-in "if not found" argument. Returns multiple columns at once. Searches from end with search_mode=-1. Wildcard matching. No column index number needed.
| Feature | VLOOKUP | INDEX MATCH | XLOOKUP |
|---|---|---|---|
| Look left of lookup column | ✗ | ✓ | ✓ |
| Built-in error handling | ✗ | ✗ | ✓ |
| Return multiple columns | ✗ | Complex | ✓ |
| Search from end (last match) | ✗ | Complex | ✓ |
| Wildcard matching | ✓ | ✓ | ✓ |
| Works in Excel 2019/earlier | ✓ | ✓ | ✗ |
| Readable syntax | Medium | Complex | ✓ Cleanest |
| Speed on large data | Slowest | Fast | Fast |
Excel 365 / 2021 users: use XLOOKUP. It replaces both VLOOKUP and INDEX MATCH with better syntax and more features. Need Excel 2019 compatibility: use INDEX MATCH. It handles everything VLOOKUP can and more. Stop using VLOOKUP for new formulas — it has no advantages over the other two in any scenario.
Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.
Start the Excel Basics track free →