VLOOKUP vs XLOOKUP

Use XLOOKUP if everyone who opens the file has Excel 2021, 2024 or Microsoft 365. It defaults to an exact match, can return columns to the left of the search column, and handles missing values without a wrapper. Use VLOOKUP when the file has to work in Excel 2019 or earlier.

The differences

Feature	VLOOKUP	XLOOKUP
Available in	Every version	Excel 2021, 2024 and Microsoft 365
Default match	Approximate, unless the 4th argument is FALSE	Exact
Which column comes back	A column number you count by hand	A range you point at
Inserting a column in the table	Can silently return the wrong column	Keeps working
Return a column to the left	Not possible	Yes
Value not found	#N/A, so you wrap it in IFERROR or IFNA	Built-in if_not_found argument
Search from the bottom up	No	Yes, with search_mode -1

Same data, side by side

One sample sheet, each approach in its own cell. Results are calculated by our formula engine.

Cell	Formula	Result	What it shows
G2	=VLOOKUP(F2,A2:D6,3,FALSE)	Sales	VLOOKUP: dept for the ID, counting 3 columns over
G3	=XLOOKUP(F2,A2:A6,C2:C6)	Sales	XLOOKUP: same answer, pointing at the Dept column
G4	=XLOOKUP("Chen Wu",B2:B6,A2:A6)	E-103	XLOOKUP looks left: the ID for a name. VLOOKUP can't
G5	=XLOOKUP("E-999",A2:A6,C2:C6,"Not found")	Not found	A missing ID returns a message instead of #N/A

Try it live

Change the inputs or rewrite a formula and compare the results.

When to use each

Choose VLOOKUP when

Colleagues or clients open the file in Excel 2019 or earlier, or a template you must match already uses it. Always pass FALSE as the last argument.

Choose XLOOKUP when

Everyone has a current version. It is shorter to write, harder to break, and reads like a sentence: find this, here, return that.

Practice XLOOKUP

From lesson 10, XLOOKUP. Graded instantly, then re-checked on changed data.

ID to name

In G2, return the name for the employee ID in F2.

The functions

VLOOKUPVLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row. Every current version.
XLOOKUPXLOOKUP searches one column or row for a value and returns the matching item from another. Excel 2021, 2024 and Microsoft 365.

Questions

Will XLOOKUP replace VLOOKUP?

VLOOKUP still works and isn't going away, so existing files keep running. For new work in current versions, XLOOKUP is the better default.

Is XLOOKUP slower than VLOOKUP?

Not in a way you'll notice in normal workbooks. Both are fast; XLOOKUP's binary search mode is faster still on very large sorted lists.

Last reviewed October 2026.