Workflow guide
VLOOKUP vs XLOOKUP vs INDEX MATCH
Use XLOOKUP if you have Excel for Microsoft 365, Excel 2021, Excel 2024, Excel for the web, or current Google Sheets: it is the most flexible and handles not-found cases cleanly. Use INDEX MATCH when you need compatibility with Excel 2019 or earlier. Use VLOOKUP only for quick right-only lookups in a stable table. This guide shows the exact difference with copyable formulas, the same sample data, and a simple decision table.
Lookup formula decision table
| Situation | Use | Why |
|---|---|---|
| You have Microsoft 365, Excel 2021, Excel 2024, Excel for the web, or Google Sheets | XLOOKUP | XLOOKUP looks in any direction, has built-in not-found handling, and is the fastest on large data. |
| You need to return a value to the left of the lookup column | XLOOKUP or INDEX MATCH | Both can look left. VLOOKUP cannot return a column that sits left of its lookup key. |
| You must support older Excel (2019 and earlier) | INDEX MATCH | INDEX MATCH works in every spreadsheet version and does not break when columns are inserted. |
| Quick right-only lookup in a small, stable table | VLOOKUP | VLOOKUP is the fastest to type when the key is in the first column and the layout will not change. |
Sample data
| SKU | Item | Category | Price |
|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 |
| A-101 | Mouse | Hardware | 25 |
| B-200 | Desk Mat | Office | 18 |
| B-201 | Notebook | Office | 7 |
Side-by-side examples
=VLOOKUP(F2,A2:D100,4,FALSE)Returns the Price (4th column) for the SKU in F2. FALSE forces an exact match.
=XLOOKUP(F2,A2:A100,D2:D100,"Not found")Returns the Price for the SKU in F2, or the text Not found instead of #N/A.
=INDEX(D2:D100,MATCH(F2,A2:A100,0))MATCH finds the row for F2; INDEX returns the value from the Price column at that row.
=INDEX(A2:A100,MATCH(49,D2:D100,0))VLOOKUP cannot do this. INDEX MATCH and XLOOKUP can look back to the left.
Which lookup formula to use
If you have Excel for Microsoft 365, Excel 2021, Excel 2024, Excel for the web, or current Google Sheets, use XLOOKUP. If you must support Excel 2019 or earlier, use INDEX MATCH. Reserve VLOOKUP for quick right-only lookups in a table whose columns will not move.
VLOOKUP: simple but limited
VLOOKUP is the fastest to type and everyone recognizes it, but it can only return columns to the right of the lookup key, and its column-number argument breaks when you insert or delete columns inside the table range.
=VLOOKUP(F2,A2:D100,4,FALSE)XLOOKUP: the modern default
XLOOKUP searches in any direction, has built-in not-found handling so you avoid #N/A, and is the fastest of the three on large data. It is available in Excel for Microsoft 365, Excel 2021, Excel 2024, Excel for the web, and current Google Sheets, but not in Excel 2016 or Excel 2019.
=XLOOKUP(F2,A2:A100,D2:D100,"Not found")INDEX MATCH: the compatible workhorse
INDEX MATCH works in every spreadsheet version, looks in any direction, and does not break when columns shift because you reference the return range directly instead of a column number.
=INDEX(D2:D100,MATCH(F2,A2:A100,0))Looking left
When the value you want sits to the left of your lookup key, VLOOKUP cannot help. Use XLOOKUP or INDEX MATCH instead.
=INDEX(A2:A100,MATCH(49,D2:D100,0))Handling not found
XLOOKUP takes a not-found argument directly. For VLOOKUP or INDEX MATCH, wrap the formula in IFERROR to return a clean message instead of #N/A.
=IFERROR(INDEX(D2:D100,MATCH(F2,A2:A100,0)),"Not found")Performance at scale
For a few thousand rows, all three are effectively instant; do not pick based on speed. On very large datasets VLOOKUP is the heaviest because it rescans the lookup column, INDEX MATCH is lighter, and XLOOKUP is most efficient. If a sheet feels slow, moving off VLOOKUP usually helps.
Excel vs Google Sheets differences
All three functions are available in current Google Sheets. For mixed Excel and Google Sheets teams, confirm the Excel version; INDEX MATCH is the safest choice when anyone uses Excel 2019 or earlier.
How the workflow fits together
- XLOOKUP is the cleanest modern choice when every user has Microsoft 365, Excel 2021, Excel 2024, Excel for the web, or current Google Sheets.
- INDEX MATCH is the most portable: it looks in any direction and survives inserted columns.
- VLOOKUP is fine for quick right-only lookups but is a trap once you need to look left or the layout changes.
- After choosing a pattern, use the matching builder to generate the formula with your own ranges.
Common mistakes
- Do not use VLOOKUP when the return column is to the left of the lookup key; use XLOOKUP or INDEX MATCH.
- Do not hardcode VLOOKUP column numbers in a table whose columns may move; the result silently shifts.
- Do not assume XLOOKUP exists in every Excel release; Excel 2016 and Excel 2019 do not support it.
- Do not leave lookups unguarded; wrap VLOOKUP or INDEX MATCH in IFERROR, or use the XLOOKUP not-found argument.
Related formulas
FAQ
Should I use XLOOKUP or INDEX MATCH?
Use XLOOKUP if every user has Microsoft 365, Excel 2021, Excel 2024, Excel for the web, or current Google Sheets; it is cleaner and handles not-found cases directly. Use INDEX MATCH when you must support Excel 2019 or earlier.
Why is VLOOKUP considered worse than the alternatives?
VLOOKUP can only return columns to the right of the lookup key and breaks when columns are inserted, because it relies on a fixed column number. XLOOKUP and INDEX MATCH avoid both problems.
Is XLOOKUP available in Google Sheets?
Yes. Current Google Sheets supports XLOOKUP. For cross-platform workbooks, confirm that Excel users have Microsoft 365, Excel 2021, Excel 2024, or Excel for the web.
Which lookup is fastest on large data?
XLOOKUP is generally the most efficient, INDEX MATCH is lighter than VLOOKUP, and VLOOKUP is heaviest because it rescans the lookup column. For small tables the difference is negligible.
Can I use these lookup examples in a real workbook?
Yes. Copy the closest formula pattern, then replace the lookup value, lookup range, and return range with the cells from your own workbook.