Function comparison · engine-measured

VLOOKUP vs XLOOKUP

XLOOKUP is the modern replacement for VLOOKUP — it looks left, defaults to exact match, handles missing values without a wrapper, and returns whole arrays. But VLOOKUP still runs everywhere, including older Excel and Google Sheets. Below, every difference is measured on identical data in a live engine, not copied from a feature list. Edit the cells and watch both formulas re-evaluate side by side.

fx
VLOOKUP(…)  vs  XLOOKUP(…)
7
differences measured live
4
won by XLOOKUP outright
0
won by VLOOKUP on capability
2021+
Excel needed for XLOOKUP
▶ Try both live See the feature matrix
🏆Verdict: XLOOKUP for new work; VLOOKUP for compatibility

If your workbook will only ever open in Excel 2021+ or Microsoft 365, use XLOOKUP: it is safer by default and does strictly more. If the file must open in Excel 2019 or earlier, LibreOffice, or older Google Sheets builds, VLOOKUP (or INDEX/MATCH) is the portable choice. There is no scenario where VLOOKUP is more capable than XLOOKUP — its only advantages are ubiquity and muscle memory.

VLOOKUP strengths

  • Runs in every Excel version since 2003, plus Google Sheets and LibreOffice
  • Slightly faster in some engine benchmarks on large sorted tables
  • Everyone on your team already knows it

XLOOKUP strengths

  • Looks left, right, up or down — lookup and return ranges are independent
  • Defaults to exact match (VLOOKUP defaults to approximate — the classic silent-wrong-answer trap)
  • Built-in if_not_found replaces the IFERROR/IFNA wrapper
  • Returns multiple columns as one dynamic array
  • Search first-to-last or last-to-first; explicit approximate-match direction
  • Survives column inserts — no hardcoded column index to break

Feature-by-feature matrix

Fourteen capabilities, scored honestly. ✓ full support, ◐ partial / with caveats, ✗ not supported. VLOOKUP's only green cells are ubiquity — every capability edge goes to XLOOKUP.

CapabilityVLOOKUPXLOOKUPWhy it matters
Look up a value to the right✓ Yes✓ YesThe baseline case — both tie.
Look up a value to the left✗ No✓ YesVLOOKUP can only return columns at or right of the table's first column.
Default match mode◐ Approximate (TRUE)✓ Exact (0)VLOOKUP's silent approximate default is the #1 wrong-answer trap on unsorted data.
Handle a missing value◐ #N/A + wrapper✓ if_not_found argXLOOKUP's 4th argument replaces IFERROR/IFNA.
Return several columns at once✗ One per formula✓ Dynamic arrayXLOOKUP spills a whole return range.
Search direction (first / last match)✗ First only✓ 1 or -1Matters with duplicate keys.
Approximate match direction◐ Next smaller only✓ -1 or +1XLOOKUP can round up or down explicitly.
Survives inserting a column✗ Index breaks✓ Range auto-shiftsVLOOKUP hardcodes col_index_num; XLOOKUP names the return range.
Return a whole row / vertical array✗ No✓ YesXLOOKUP's return range can be 2-D.
Wildcard match (* ?)✓ Yes✓ Yes (mode 2)Both support wildcards; XLOOKUP needs match_mode 2.
Binary search on sorted data◐ Approximate only✓ search_mode 2/-2XLOOKUP exposes binary search explicitly.
Excel 2019 and earlier✓ Yes✗ No (#NAME?)XLOOKUP needs Excel 2021 / Microsoft 365.
Google Sheets✓ Yes✓ YesBoth supported in current Google Sheets.
LibreOffice Calc✓ Yes◐ 7.2+ onlyOlder LibreOffice builds lack XLOOKUP.

Syntax, side by side

The structural difference: VLOOKUP counts columns from a fixed table edge; XLOOKUP names the lookup and return ranges independently.

VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_valueWhat to find. Must live in the FIRST column of table_array.
table_arrayThe whole block, e.g. A2:E6. The lookup column is always its left edge.
col_index_numWhich column to return, counted from 1. Hardcoded — breaks if you insert a column.
range_lookupOptional. TRUE (default!) = approximate, FALSE = exact. Omitting it is the classic trap.
=VLOOKUP(103, A2:E6, 4, FALSE)

XLOOKUP

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_valueWhat to find.
lookup_arrayThe single column/row to search — independent of the return range.
return_arrayThe column/row (or 2-D block) to return. Can sit anywhere, left or right.
if_not_foundOptional. Value to return instead of #N/A when nothing matches.
match_modeOptional. 0 exact (default), -1 next smaller, 1 next larger, 2 wildcard.
search_modeOptional. 1 first-to-last (default), -1 last-to-first, 2/-2 binary search.
=XLOOKUP(103, A2:A6, D2:D6)

Live head-to-head playground

One editable dataset, two formulas. Pick a scenario or type your own — both sides re-evaluate in the same engine, so you see exactly where they diverge.

A1Sample data — click any cell to edit; both formulas re-evaluate live

VLOOKUP

—

XLOOKUP

—
HyperFormula 3.5 · live

Every difference, measured

These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.

Exact match, return a right-hand column

Both find ID 103 and return its salary. The baseline case where they tie.
VLOOKUP
=VLOOKUP(103,A2:E6,4,FALSE)
105000
XLOOKUP
=XLOOKUP(103,A2:A6,D2:D6)
105000
Identical result. VLOOKUP needs the column index (4); XLOOKUP names the return range directly.

Left lookup — return a column to the LEFT of the lookup column

Look up by Name (column B) and return ID (column A). VLOOKUP cannot return a column left of its table_array's first column.
VLOOKUP
=VLOOKUP("Carol",B2:E6,1,FALSE)
Carol
XLOOKUP
=XLOOKUP("Carol",B2:B6,A2:A6)
103
VLOOKUP searches the first column of B2:E6 (Names) but column index 1 returns the Name itself, not the ID — it structurally cannot reach column A. XLOOKUP decouples lookup and return ranges.

Default match mode when the last argument is omitted

Omit the match-mode argument. VLOOKUP defaults to approximate (TRUE); XLOOKUP defaults to exact (0).
VLOOKUP
=VLOOKUP(103,A2:E6,4)
105000
XLOOKUP
=XLOOKUP(103,A2:A6,D2:D6)
105000
On this sorted key both happen to return 105000, but VLOOKUP's silent approximate default is the #1 source of wrong-but-plausible answers on unsorted data.

Missing lookup value

Search for ID 999, which does not exist. VLOOKUP throws #N/A; XLOOKUP returns a custom if_not_found value.
VLOOKUP
=VLOOKUP(999,A2:E6,4,FALSE)
#N/A
XLOOKUP
=XLOOKUP(999,A2:A6,D2:D6,"Not found")
Not found
XLOOKUP's built-in 4th argument replaces the IFERROR/IFNA wrapper VLOOKUP needs.

Return several columns at once

Return Name, Dept and Salary for ID 103 in one formula. XLOOKUP spills a horizontal array; VLOOKUP returns one column per formula.
VLOOKUP
=VLOOKUP(103,A2:E6,2,FALSE)
Carol
XLOOKUP
=XLOOKUP(103,A2:A6,B2:D6)
Carol | Eng | 105000
XLOOKUP returns the whole B2:D6 slice as a dynamic array; VLOOKUP would need three separate formulas (or CHOOSECOLS).

Approximate match on a sorted key

Look up 103.5 (between keys). VLOOKUP TRUE and XLOOKUP match_mode -1 both fall back to the next smaller key (103).
VLOOKUP
=VLOOKUP(103.5,A2:E6,4,TRUE)
105000
XLOOKUP
=XLOOKUP(103.5,A2:A6,D2:D6,"",-1)
105000
Both support approximate match; XLOOKUP makes the direction explicit (-1 next smaller, 1 next larger).

Search from the last occurrence

On a separate table with duplicate keys (a, b, a, c), XLOOKUP's search_mode lets you pick the first (1) or last (-1) match. VLOOKUP always returns the first.
VLOOKUP
=VLOOKUP("a",A2:B5,2,FALSE)
1
XLOOKUP
=XLOOKUP("a",A2:A5,B2:B5,"",0,-1)
3
VLOOKUP and XLOOKUP-first both return 1; XLOOKUP-last returns 3. VLOOKUP has no search-direction argument. Measured on a dedicated duplicate-key table, not the main dataset above.

Engine benchmark

500 exact-match lookups against tables of increasing size, timed in this page's engine.

Table sizeVLOOKUPXLOOKUP
1,000 rows × 500 lookups383.16 ms363.51 ms
5,000 rows × 500 lookups4237.71 ms4319.24 ms
10,000 rows × 500 lookups15639.3 ms15689.95 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

A decision rule for the situations that actually come up. The short version: XLOOKUP for new controlled-environment work, VLOOKUP (or INDEX/MATCH) when the file must open elsewhere.

SituationUseWhy
Building a new workbook for Excel 2021 / Microsoft 365XLOOKUPXLOOKUP is safer by default and strictly more capable. No reason to choose VLOOKUP here.
The file must open in Excel 2019 or earlierVLOOKUPXLOOKUP returns #NAME? on old builds. Use VLOOKUP, or INDEX/MATCH for left lookups.
You need to return a column to the LEFT of the keyXLOOKUPVLOOKUP structurally cannot. XLOOKUP does it natively; the old workaround is INDEX/MATCH.
Sharing with mixed / unknown Excel versionsVLOOKUPVLOOKUP is the lowest common denominator. INDEX/MATCH if you also need left lookups.
The lookup key has duplicates and you want the LAST matchXLOOKUPXLOOKUP search_mode -1. VLOOKUP always returns the first.
You want a clean default instead of #N/A on missing keysXLOOKUPXLOOKUP's if_not_found argument. With VLOOKUP you wrap in IFNA/IFERROR.
Returning several adjacent columns for one keyXLOOKUPOne XLOOKUP spills the whole block; VLOOKUP needs one formula per column.
Maximum raw speed on a huge sorted tableEitherEngine-dependent and usually marginal. In our HyperFormula benchmark the two were within a few percent. Measure your own data.

VLOOKUP → XLOOKUP migration

The six translations you will actually make. Note how the IFNA wrapper and the INDEX/MATCH workaround both collapse into a single XLOOKUP.

TaskVLOOKUP / old wayXLOOKUPWhat changed
Exact match, return right column=VLOOKUP(A2, D2:G100, 3, FALSE)=XLOOKUP(A2, D2:D100, F2:F100)Drop the column index; name the return column. Exact is now the default.
Missing value → blank instead of #N/A=IFNA(VLOOKUP(A2, D2:G100, 3, FALSE), "")=XLOOKUP(A2, D2:D100, F2:F100, "")The IFNA wrapper collapses into the 4th argument.
Left lookup (return a column left of the key)=INDEX(A2:A100, MATCH(E2, B2:B100, 0))=XLOOKUP(E2, B2:B100, A2:A100)The INDEX/MATCH workaround becomes a single XLOOKUP.
Approximate match, next smaller=VLOOKUP(A2, D2:G100, 3, TRUE)=XLOOKUP(A2, D2:D100, F2:F100, "", -1)match_mode -1 makes the direction explicit; data must still be sorted.
Return three columns at once=VLOOKUP(A2,D2:G100,2,FALSE) & …3… & …4…=XLOOKUP(A2, D2:D100, E2:G100)One formula spills three columns instead of three separate VLOOKUPs.
Last match among duplicates(not possible — first match only)=XLOOKUP(A2, D2:D100, F2:F100, "", 0, -1)search_mode -1 searches bottom-up.

Frequently asked questions

Is XLOOKUP faster than VLOOKUP?
Not reliably. In our HyperFormula 3.5 benchmark over 1,000–10,000 rows the two were within a few percent of each other, with no consistent winner. Real Excel performance depends on version, whether the data is sorted, and the calculation chain. Choose XLOOKUP for correctness and flexibility, not for a promised speedup.
Can XLOOKUP look to the left?
Yes — that is its headline advantage. Because the lookup_array and return_array are separate arguments, the return column can sit anywhere, including to the left of the key. VLOOKUP cannot do this at all; the traditional workaround is INDEX/MATCH.
Why does VLOOKUP return the wrong value when I omit the last argument?
VLOOKUP's range_lookup defaults to TRUE (approximate match), which assumes the first column is sorted and returns the next-smaller key. On unsorted data this silently returns a plausible but wrong answer. Always pass FALSE for exact match — or use XLOOKUP, which defaults to exact.
Does XLOOKUP work in Google Sheets?
Yes, current Google Sheets supports XLOOKUP with the same arguments. Older Excel (2019 and earlier) and older LibreOffice builds do not — they return #NAME?. For maximum portability use VLOOKUP or INDEX/MATCH.
Should I replace every VLOOKUP with XLOOKUP?
Only if the workbook will stay in Excel 2021+ / Microsoft 365 or current Google Sheets. If it is shared with people on older Excel, replacing VLOOKUP with XLOOKUP will break their view (#NAME?). For new, controlled-environment work, XLOOKUP is the better default.
What is the closest portable alternative to XLOOKUP?
INDEX/MATCH. It looks left, defaults to exact match (with MATCH's 0), and works in every Excel version and Google Sheets. It is more verbose than XLOOKUP but is the standard fallback when XLOOKUP is unavailable.
How do I handle a missing value in each?
XLOOKUP has a built-in if_not_found argument: XLOOKUP(key, look, ret, "Not found"). VLOOKUP throws #N/A, so you wrap it: IFNA(VLOOKUP(...), "Not found"). Same outcome, one fewer function with XLOOKUP.