Lookup & reference
VLOOKUP vs XLOOKUP in Excel (Which Should You Use?)
VLOOKUP and XLOOKUP both find a value in a table and return a matching value, but XLOOKUP is the modern rewrite: simpler, more flexible, and harder to break.
This guide runs the same lookups side by side so you can see the differences, then helps you decide which to use based on flexibility and which Excel version your audience runs.
VLOOKUP vs XLOOKUP syntax
=XLOOKUP(lookup, lookup_array, return_array) vs =VLOOKUP(lookup, table, col_index, FALSE) | Argument | Required | What it does |
|---|---|---|
lookup_value | Required | The value you search for, identical input in both functions. |
lookup_array / table_array | Required | XLOOKUP takes the single search column; VLOOKUP takes the whole table and only searches its leftmost column. |
return_array / col_index_num | Required | XLOOKUP takes the column to return (any side); VLOOKUP takes a column NUMBER counted from the left. |
match / range_lookup | Optional | XLOOKUP defaults to an exact match; VLOOKUP needs FALSE as its 4th argument for an exact match. |
Note: XLOOKUP (Microsoft 365 / Excel 2021+ / Google Sheets) is the modern choice; VLOOKUP wins only for compatibility with Excel 2019 and earlier.
Examples
Real, copy-paste-ready formulas.
1. The same price lookup, both ways
=XLOOKUP(E2, A:A, C:C) vs =VLOOKUP(E2, A:C, 3, FALSE) Same result. XLOOKUP names the return column directly; VLOOKUP counts to the 3rd column and needs FALSE for an exact match.
2. Return a column to the LEFT
=XLOOKUP(E2, C:C, A:A) vs (VLOOKUP cannot do this) XLOOKUP returns any column, so a left lookup is no problem. VLOOKUP can only look right of its lookup column, so this needs XLOOKUP or INDEX/MATCH.
3. Handle a missing value cleanly
=XLOOKUP(E2, A:A, C:C, "Not found") vs =IFERROR(VLOOKUP(E2, A:C, 3, FALSE), "Not found") XLOOKUP has a built-in if_not_found argument; with VLOOKUP you must wrap the whole formula in IFERROR to avoid #N/A.
How to write VLOOKUP vs XLOOKUP step by step
- 1
Check compatibility first: if everyone uses Microsoft 365 or Excel 2021+, prefer XLOOKUP.
- 2
For a new lookup, type =XLOOKUP( then the value, the search column, and the return column.
- 3
If you must support Excel 2019 or earlier, use =VLOOKUP( with the table and a column number, ending in FALSE.
- 4
Remember XLOOKUP can look left and defaults to exact match; VLOOKUP cannot look left and needs FALSE.
- 5
To convert an old VLOOKUP, replace the table-plus-number with separate lookup and return columns in XLOOKUP.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#N/A | The lookup value is not found by either function. | Check for stray spaces and type mismatches; add an if_not_found in XLOOKUP, or wrap VLOOKUP in IFERROR. |
#NAME? | XLOOKUP is not available in your Excel version. | Update to Microsoft 365 / Excel 2021+, or fall back to VLOOKUP or INDEX/MATCH. |
#REF! (VLOOKUP) | col_index_num is larger than the table width, or a column was deleted. | Switch to XLOOKUP, which references the return column directly and does not break when columns move. |
Wrong value (VLOOKUP) | range_lookup was omitted, so VLOOKUP did an approximate match. | Add FALSE as the fourth argument, or use XLOOKUP, which is exact by default. |
Frequently asked questions
Should I switch to XLOOKUP?
If your audience is on Microsoft 365 or Excel 2021+, yes. XLOOKUP is simpler, looks both directions, defaults to exact match, and does not break when columns move. Keep VLOOKUP only for older-version compatibility.
Is XLOOKUP faster than VLOOKUP?
For single lookups the difference is negligible. On very large sheets XLOOKUP can be more efficient because it scans only the lookup and return columns rather than a whole table.
Why does my XLOOKUP show #NAME?
Your Excel version predates XLOOKUP, so it needs Microsoft 365 or Excel 2021+. Use VLOOKUP or INDEX/MATCH in older versions.
What about INDEX/MATCH?
INDEX/MATCH also looks left and survives column inserts, making it the best lookup in older Excel that lacks XLOOKUP.