VLOOKUP vs INDEX/MATCH vs XLOOKUP
All three functions look up a value in a table and return a related result — but they differ enormously in flexibility, direction, and Excel version support.
VLOOKUP
Syntax
=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])Example
=VLOOKUP(A2, ProductTable, 3, FALSE)✓ Pros
- • Simple, widely understood
- • Fast for simple lookups
- • Available in all Excel versions
✗ Cons
- • Can only look right (never left)
- • Breaks when columns are inserted
- • Returns first match only
- • Column index is fragile (hard-coded number)
Use when…
Your lookup column is on the far left and you only need one match. It's quick for simple scenarios in shared workbooks where others know VLOOKUP.
INDEX/MATCH
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))Example
=INDEX(C:C, MATCH(A2, B:B, 0))✓ Pros
- • Look left or right
- • Stable when columns are inserted
- • More flexible — reference full columns
- • Slightly faster on large datasets
✗ Cons
- • More complex syntax — two functions
- • Steeper learning curve
- • Verbose for simple cases
Use when…
You need to look up in any direction, your columns might be rearranged, or you're building a robust model. The power-user standard before XLOOKUP arrived.
XLOOKUP
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Example
=XLOOKUP(A2, B:B, C:C, "Not found")✓ Pros
- • Simplest syntax for most cases
- • Works in any direction
- • Built-in "not found" fallback
- • Returns entire rows/columns
- • Exact match by default
✗ Cons
- • Excel 365 / 2021+ only
- • Not available in Google Sheets (use XLOOKUP alternative via named ranges)
Use when…
You're on Excel 365 or Excel 2021+. For almost every lookup task, XLOOKUP is the cleanest and most readable solution. Start here by default.
⚡ Quick Verdict
Use **XLOOKUP** if you're on Excel 365/2021. Use **INDEX/MATCH** for robust formulas that need to work in all Excel versions or look left. Use **VLOOKUP** only when simplicity and backwards compatibility matter most.
Try these formulas in the generator
Describe what you need in plain English and get the exact formula — including the right choice between these options.