XLOOKUP vs VLOOKUP — six differences that actually matter.
For new formulas on Excel 365, 2021, or Google Sheets, XLOOKUP wins on every axis except one — backward compatibility with Excel 2019 and older. Below: the six aspects where the two functions actually behave differently, an interactive side-by-side demo, and the copy-paste migration patterns to retire your VLOOKUPs cleanly.
| Aspect | VLOOKUP | XLOOKUP |
|---|---|---|
| Basic syntax — find Carol's dept by ID | =VLOOKUP(F2, A2:D6, 3, FALSE) | =XLOOKUP(F2, A2:A6, C2:C6)★ |
| Search direction | Right of the lookup column only | Left or right — any return range★ |
| Return column reference | Integer index (3) — brittle when columns get inserted | Named range — survives column inserts★ |
| Not-found handling | Returns #N/A — needs an IFERROR wrap | Built-in 4th arg if_not_found★ |
| Match modes | Exact (FALSE) or approximate (TRUE) only | Exact · approximate · wildcard · regex★ |
| Excel version required | Every version back to Excel 97★ | Excel 365 / 2021+ · Google Sheets (since 2022) |
The verdict, before the details
One sentence per scenario — which function to reach for.
| Your situation | Reach for |
|---|---|
| Writing a fresh formula in Excel 365 / 2021 / Sheets | XLOOKUP — cleaner syntax, fewer ways to break |
| Lookup column is to the right of what you want returned | XLOOKUP — VLOOKUP physically can't do this |
| You want a custom “Not found” message | XLOOKUP — 4th arg if_not_found; no IFERROR wrap |
| Workbook will be opened in Excel 2019 or older | VLOOKUP — XLOOKUP returns #NAME? there |
| Inheriting a sheet with hundreds of working VLOOKUPs | Leave them. Migrate only when you next touch each formula |
| Need wildcard / regex matching | XLOOKUP with match_mode=2 |
Six differences in detail
Each axis with the concrete formula change and why it matters.
Diff 1 — Two ranges vs one range + column index
VLOOKUP's 3rd argument is an integer column index counted from the left of the table. Insert a column anywhere inside the table and that integer silently points at the wrong column — a classic silent-bug source. XLOOKUP takes the return range directly, by reference. Inserting a column doesn't change which range is named.
=VLOOKUP(F2, A2:D6, 3, FALSE)XLOOKUP
=XLOOKUP(F2, A2:A6, C2:C6)Identical result. The XLOOKUP version explicitly names the return column — and stays correct if you later insert a column B'.
Diff 2 — Search direction (XLOOKUP can go left)
VLOOKUP can only return values from columns to the right of the lookup column. If your lookup column is column B and you want a value from column A, VLOOKUP simply can't do it without flipping the entire table or using INDEX(MATCH(…)). XLOOKUP doesn't care — pass any return range, anywhere.
VLOOKUP Impossible without restructuring the table.
XLOOKUP
=XLOOKUP("Carol", B2:B6, A2:A6) → returns "E003".Diff 3 — Default match type (exact vs approximate)
A subtle but consequential difference: VLOOKUP's 4th argument range_lookup defaults to TRUE (approximate match) if omitted. Approximate match against unsorted data returns wrong answers silently. XLOOKUP defaults to exact match — what 95% of spreadsheet users actually want.
=VLOOKUP(F2, A2:D6, 3) ← no 4th arg, defaults to TRUE, can silently return the wrong row.Safer Always pass
FALSE explicitly to VLOOKUP. With XLOOKUP, you can omit the match-mode argument and still get exact-match safety by default.Diff 4 — Built-in if_not_found vs IFERROR wrap
VLOOKUP returns #N/A when the lookup value isn't found. The standard remedy is wrapping in IFERROR — which also masks other errors like #REF! and #VALUE! that may indicate real bugs. XLOOKUP's 4th argument fires only on the no-match path, leaving genuine errors loud.
=IFERROR(VLOOKUP(F2, A2:D6, 3, FALSE), "Not found")XLOOKUP
=XLOOKUP(F2, A2:A6, C2:C6, "Not found")Same result; XLOOKUP only catches the no-match case so structural bugs surface instead of disappearing. Deep dive on if_not_found →
Diff 5 — Match modes (wildcards, regex, approximate)
XLOOKUP's 5th argument match_mode takes four values: 0 (exact, default), -1 (exact-or-smaller), 1 (exact-or-larger), and 2 (wildcard — ? and *). Google Sheets' XLOOKUP also accepts 3 for regex. VLOOKUP has only exact and approximate.
=XLOOKUP("*desk*", B2:B6, A2:A6, , 2)Finds the first product whose name contains desk (Standing Desk, Monitor stand, etc.). VLOOKUP has no equivalent — wildcards in VLOOKUP's lookup_value work, but not as a separate match-mode argument.
Diff 6 — Excel version requirements
This is the only axis where VLOOKUP wins. XLOOKUP shipped to Excel for Microsoft 365 in 2019 and to Excel 2021 (perpetual license) when that released. On Excel 2019 or earlier, typing =XLOOKUP(...) returns #NAME?. VLOOKUP works in every Excel version since Excel 97. Google Sheets added XLOOKUP in August 2022.
INDEX(MATCH(...)) which works everywhere and matches XLOOKUP's capabilities on older versions.When VLOOKUP still wins
Three real scenarios where VLOOKUP is the right tool.
The Excel internet over-rotated on “always use XLOOKUP” — but there are pragmatic cases where VLOOKUP stays correct.
Case 1 — Mixed Excel-version teams
You share workbooks with users on Excel 2019, 2016, or 2010 (common in regulated industries, enterprise IT freezes, finance teams with locked-down installs). XLOOKUP fails with #NAME? there. VLOOKUP doesn't.
Case 2 — Existing sheets with hundreds of working VLOOKUPs
A sheet with 200 working VLOOKUPs is correct. Mass-rewriting to XLOOKUP is project-grade work with non-zero risk of introducing new bugs. The pragmatic rule: migrate incrementally. When you next touch a formula for another reason, rewrite that one to XLOOKUP. Don't do a big-bang rewrite.
Case 3 — Approximate lookup over sorted data
For sorted lookups (commission tiers, grade thresholds, tax brackets), VLOOKUP with TRUE as the 4th argument is well-understood and battle-tested. XLOOKUP supports the same pattern via match_mode=-1, but the VLOOKUP form is more recognisable to readers who know the old syntax. Either works.
Migrating existing VLOOKUPs to XLOOKUP
Four common VLOOKUP shapes and their direct XLOOKUP equivalents.
| Old VLOOKUP | New XLOOKUP |
|---|---|
=VLOOKUP(F2, A2:D6, 3, FALSE) | =XLOOKUP(F2, A2:A6, C2:C6) |
=IFERROR(VLOOKUP(F2, A2:D6, 3, FALSE), "-") | =XLOOKUP(F2, A2:A6, C2:C6, "-") |
=VLOOKUP(score, A2:B6, 2, TRUE) ← approx match | =XLOOKUP(score, A2:A6, B2:B6, , -1) ← match_mode=-1 |
| Lookup → return value to the LEFT — impossible with VLOOKUP | =XLOOKUP(name, B2:B6, A2:A6) ← XLOOKUP only |
Find & Replace shortcut. Ctrl+H with "within: Sheet" and "look in: Formulas" won't do the full rewrite (the argument shape changes), but it'll find every VLOOKUP fast for you to rewrite by hand.
Excel & Sheets compatibility
The compatibility gap is the entire reason VLOOKUP still exists. Here's where each function is supported.
| Platform | VLOOKUP | XLOOKUP |
|---|---|---|
| Excel for Microsoft 365 (any plan) | ✅ Yes | ✅ Yes (since 2019) |
| Excel 2021 (perpetual license) | ✅ Yes | ✅ Yes |
| Excel 2019 | ✅ Yes | ❌ No — returns #NAME? |
| Excel 2016 / 2013 / 2010 / 2007 | ✅ Yes | ❌ No |
| Excel 97 → 2003 | ✅ Yes (since Excel 97) | ❌ No |
| Excel for the web | ✅ Yes | ✅ Yes |
| Google Sheets | ✅ Yes | ✅ Yes (since Aug 2022) |
| LibreOffice Calc | ✅ Yes | ✅ Yes (since 7.3) |
Related
The two main references and the error-diagnostic deep dives.
FAQ
6.01Is XLOOKUP better than VLOOKUP?▸
For new formulas on Excel 365, 2021, or Google Sheets — yes, XLOOKUP wins on every axis except backward compatibility. It can return values to the left, takes ranges directly (no column-index counting), has a built-in if_not_found argument so you don't need IFERROR wrappers, and supports wildcard and regex match modes. The one place VLOOKUP still beats it is older Excel versions (2019 and earlier) where XLOOKUP doesn't exist at all.
6.02What's the main difference between XLOOKUP and VLOOKUP?▸
Six concrete differences. (1) XLOOKUP takes two range references; VLOOKUP takes one range and an integer column-index. (2) XLOOKUP can return values to the left of the lookup column; VLOOKUP can only return values to the right. (3) XLOOKUP has if_not_found as its 4th argument; VLOOKUP needs IFERROR wrapping. (4) XLOOKUP defaults to exact match; VLOOKUP defaults to approximate match (a common bug source). (5) XLOOKUP supports wildcards and regex via match_mode; VLOOKUP only does exact or approximate. (6) XLOOKUP requires Excel 365/2021 or Google Sheets; VLOOKUP works in every Excel version.
6.03Can XLOOKUP fully replace VLOOKUP?▸
On Excel 365, Excel 2021, Excel for the web, or Google Sheets — yes, every VLOOKUP formula can be rewritten as a simpler XLOOKUP. On Excel 2019 or older, no — XLOOKUP simply doesn't exist there. If you share workbooks with users on older Excel, keep VLOOKUP for compatibility.
6.04How do I convert a VLOOKUP formula to XLOOKUP?▸
Replace =VLOOKUP(value, table, col_index, FALSE) with =XLOOKUP(value, first_column_of_table, return_column_of_table). The two range arguments replace the table + column-index pair. For approximate-match VLOOKUPs (TRUE 4th arg), use XLOOKUP's 5th argument match_mode=-1 (smaller match) or match_mode=1 (larger match). For IFERROR-wrapped VLOOKUPs that return a default on miss, move the default into XLOOKUP's 4th argument if_not_found — no wrapper needed.
6.05Why is XLOOKUP not available in my Excel?▸
XLOOKUP shipped with Excel for Microsoft 365 in 2019 and was added to Excel 2021 as a perpetual license. If you're on Excel 2019, Excel 2016, or older, XLOOKUP doesn't exist — typing =XLOOKUP(...) returns #NAME?. Either upgrade Excel, or use INDEX(MATCH(...)) as the closest XLOOKUP-equivalent on older versions. =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) matches XLOOKUP's behaviour for exact lookups and supports left-lookups too.
6.06Is XLOOKUP slower than VLOOKUP?▸
In practice, no — modern Excel optimizes both, and for the lookup sizes most spreadsheets actually use (thousands of rows, not millions) the difference is unmeasurable. XLOOKUP can be marginally faster than VLOOKUP because it uses binary-search on sorted data when you pass search_mode=2; VLOOKUP's approximate-match (TRUE) also uses binary search. Both are O(log n) when configured for sorted lookups and O(n) for exact-match against unsorted data.