XLOOKUP vs VLOOKUP: which should you use?

Use XLOOKUP whenever everyone who opens the file has Excel for Microsoft 365 or Excel 2021 and later. It finds exact matches by default, can return a column to the left, keeps working when columns are inserted, and has its own “not found” value. Use VLOOKUP, or better INDEX and MATCH, only when the file must open in Excel 2019 or older.

The same lookup, written both ways

Find the price of the code in E2. Both formulas return the same answer here:

F2fx=XLOOKUP(E2,A2:A6,C2:C6)

Row	A	B	C	D	E	F
1	Code	Product	Price		Find	Price
2	A-101	Keyboard	$49.00		A-104	$59.00
3	A-102	Mouse	$19.00
4	A-103	Monitor	$189.00
5	A-104	Webcam	$59.00
6	A-105	Headset	$79.00

The difference is in what each formula says. XLOOKUP reads as “find E2 in the codes, return the prices”. VLOOKUP reads as “find E2 in the first column of this table, return its 3rd column, exactly”. That 3 and that FALSE are where VLOOKUP goes wrong.

The five differences that matter

	XLOOKUP	VLOOKUP
Default match	Exact	Approximate, unless you add FALSE
Return column	A range you point at	A number you count
Look to the left	Yes	No
After inserting a column	Still right	Returns the wrong column
When nothing is found	Its 4th argument	#N/A, unless wrapped in IFNA

1. Exact match by default

Forget FALSE in VLOOKUP and it switches to an approximate match that assumes sorted data. On unsorted codes that can return a neighbouring row’s price with no error at all, the worst kind of mistake. XLOOKUP is exact unless you ask otherwise.

2. Inserting a column doesn’t break it

Insert a “Colour” column between Product and Price, and =VLOOKUP(E2,A2:D6,3,FALSE) now returns the colour: the 3 still counts from the left. XLOOKUP’s return range moves with the prices, so it keeps returning prices.

3. Looking left

VLOOKUP can only return columns to the right of the one it searches. Finding a product’s code from its name needs XLOOKUP, or INDEX and MATCH.

4. A value for “not found”

=XLOOKUP(E2,A2:A6,C2:C6,"Not listed") handles a missing code in one function. VLOOKUP needs =IFNA(VLOOKUP(E2,A2:C6,3,FALSE),"Not listed"). More on that in #N/A error in Excel.

5. Where it works

VLOOKUP works in every version of Excel. XLOOKUP needs Excel for Microsoft 365, Excel 2021 or later, or Excel for the web; older versions show #NAME?.

When VLOOKUP is still the right call

• The workbook goes to people on Excel 2019 or older. (INDEX and MATCH is still the stronger choice there.)
• You’re editing an existing model full of VLOOKUPs, and consistency matters more than switching one formula.
• An interview test asks for it by name. Many still do, so know both.