XLOOKUP vs VLOOKUP: When to Switch and What to Watch Out For
XLOOKUP vs VLOOKUP: When to Switch and What to Watch Out For
If you’ve been using VLOOKUP for years, switching to XLOOKUP feels like finally getting a power tool after years with a hand drill. But ‘XLOOKUP is better’ isn’t a complete answer — it’s available only in Excel 365/2021 and Google Sheets (since 2023), it behaves differently with error handling, and there are a few specific situations where the classic approach is actually cleaner.
This guide skips the intro-level explanation of what a lookup function does. What follows is a direct comparison across the scenarios that actually cause problems, with formulas you can paste in and adapt.
The Syntax, Side by Side
VLOOKUP takes a table range and a column number. XLOOKUP takes separate ranges for the lookup column and the return column. That single change eliminates the most common VLOOKUP failure — the formula breaking when you insert a column into your table.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Why this matters: In VLOOKUP, the third argument (col_index_num) is a hardcoded number. Insert a column to the left of your return column and every formula referencing that column is now off by one. XLOOKUP references the return column directly, so insertions don’t break anything.
Where XLOOKUP Wins Outright
1. Looking Up Values to the Left
VLOOKUP can only return values from columns to the right of the lookup column. XLOOKUP has no such restriction.
=XLOOKUP(A2, D:D, B:B) ← return from a column LEFT of the lookup
The equivalent with VLOOKUP requires INDEX-MATCH:
=INDEX(B:B, MATCH(A2, D:D, 0))
2. Handling Not-Found Errors Without IFERROR
VLOOKUP returns #N/A when no match is found. The standard workaround wraps it in IFERROR:
=IFERROR(VLOOKUP(A2, B:C, 2, 0), "Not found")
XLOOKUP has an [if_not_found] argument built in — and crucially, it still surfaces real formula errors that IFERROR would silently suppress:
=XLOOKUP(A2, B:B, C:C, "Not found")
3. Returning Multiple Columns at Once
XLOOKUP can return an entire range at once by expanding the return_array — three columns from a single formula:
=XLOOKUP(A2, B:B, C:E) ← returns values from columns C, D, and E
Where VLOOKUP Still Has a Case
Compatibility with older Excel versions. If your workbook will be opened by people using Excel 2016 or earlier, XLOOKUP formulas will show as errors. VLOOKUP goes back to Excel 2003. For shared workbooks where you can’t control recipients’ software versions, VLOOKUP is the safer choice.
The Error That Looks Identical But Isn’t
Both functions return #N/A when a lookup value isn’t found, but there are two different root causes:
Reason 1: The value genuinely isn’t in the lookup range. This is a data problem, not a formula problem.
Reason 2: The value exists but the data types don’t match. A number formatted as text and an actual number look identical in a cell but are not equal in a formula.
To diagnose a type mismatch:
=ISNUMBER(A2) → TRUE if A2 is a real number =ISTEXT(A2) → TRUE if A2 is stored as text
If your lookup value is text but your lookup range contains numbers (common in CSV imports):
=XLOOKUP(TEXT(A2,"0"), B:B, C:C)
Common gotcha: Trailing spaces cause the same mismatch. Run =LEN(A2) and =LEN(TRIM(A2)) on both sides — if the numbers differ, spaces are your problem. Fix with: =XLOOKUP(TRIM(A2), B:B, C:C)
Quick Reference: VLOOKUP → XLOOKUP Conversion
| VLOOKUP formula | XLOOKUP equivalent |
|---|---|
| =VLOOKUP(A2,B:D,2,0) | =XLOOKUP(A2,B:B,C:C) |
| =IFERROR(VLOOKUP(A2,B:D,2,0),””) | =XLOOKUP(A2,B:B,C:C,””) |
| =VLOOKUP(A2,B:D,3,0) | =XLOOKUP(A2,B:B,D:D) |
| N/A — left lookup not possible | =XLOOKUP(A2,D:D,B:B) |
| =VLOOKUP(A2,B:F,{2,3},0) | =XLOOKUP(A2,B:B,C:D) |
The One Migration Mistake to Avoid
When converting VLOOKUP to XLOOKUP, the most common mistake is treating XLOOKUP’s return_array like a column index number. In VLOOKUP, you say ‘return column 3 of my table range.’ In XLOOKUP, you reference the actual column directly.
If your VLOOKUP was =VLOOKUP(A2, B:E, 3, 0) — returning the third column of B:E, which is column D — your XLOOKUP is:
=XLOOKUP(A2, B:B, D:D) ✓ correct =XLOOKUP(A2, B:B, 3) ✗ invalid