Short answer: use XLOOKUP if your version of Excel or Google Sheets supports it. Use INDEX MATCH for older versions or two-way lookups. Use VLOOKUP only for simple, quick lookups where the lookup column is first.
Differences at a glance
- VLOOKUP looks right from the first column of a range. It breaks if you insert columns, because it uses a column number.
- INDEX MATCH can look left or right and is not affected by inserted columns.
- XLOOKUP can look in any direction, returns an exact match by default, and lets you set a value for “not found”.
Examples
=XLOOKUP(A2, Prices[Item], Prices[Price], "Not found")
=INDEX(Prices[Price], MATCH(A2, Prices[Item], 0))
=VLOOKUP(A2, Prices!A:C, 3, FALSE)
Which should you choose?
- New files in Microsoft 365, Excel 2021 or Google Sheets: XLOOKUP.
- Files shared with people on Excel 2016 or earlier: INDEX MATCH.
- Quick one-off lookups: VLOOKUP is fine.
If your workbook is full of lookups and has become slow, consider whether it needs a rebuild.
Frequently asked questions
Is XLOOKUP faster than VLOOKUP?
On large data, XLOOKUP and INDEX MATCH are generally more efficient than VLOOKUP over whole tables, and they are more robust when columns change.
Does XLOOKUP work in Google Sheets?
Yes, Google Sheets supports XLOOKUP.
Why does my lookup return #N/A?
The value is not found, often because of extra spaces, different data types (text vs number) or a mismatched range.
Need help? Tell us about your project and get a clear plan and fixed price within one working day.