XLOOKUP vs VLOOKUP in Google Sheets: the 2-minute version

XLOOKUP searches the SKU range and returns the matching price

Visual guideThe highlighted columns match XLOOKUP's separate search and return ranges. The example uses the original product table and an explicit missing-value result.

Takeaway

Use XLOOKUP for new sheets. VLOOKUP isn’t broken, but it counts columns by number and guesses when you forget FALSE. Both mistakes give you a wrong answer instead of an error.

I ran every formula below in a scratch Google Sheet on 2026-10-14. The results are what Sheets actually returned.

VLOOKUP vs XLOOKUP side by side: what each one does by default

The test sheet

A tiny product list on a tab called Products:

Row	A: SKU	B: Product	C: Price	D: Stock
2	P-100	Notebook	4.50	12
3	P-101	Pen (blue)	1.20	40
4	P-102	Stapler	9.90	0
5	P-103	Marker set	7.25	5
6	P-104	Desk lamp	27.50	3

Side by side

Job	VLOOKUP	XLOOKUP	What I got
Price of P-102	`=VLOOKUP("P-102",A2:D6,3,FALSE)`	`=XLOOKUP("P-102",A2:A6,C2:C6)`	9.9 / 9.9
SKU of “Stapler” (look left)	`=VLOOKUP("Stapler",A2:D6,1,FALSE)`	`=XLOOKUP("Stapler",B2:B6,A2:A6)`	#N/A / P-102
Missing SKU	`=VLOOKUP("P-999",A2:D6,3,FALSE)`	`=XLOOKUP("P-999",A2:A6,C2:C6,"not found")`	#N/A / not found
Whole row for P-103	three formulas	`=XLOOKUP("P-103",A2:A6,B2:D6)`	Marker set, 7.25, 5

VLOOKUP only searches the first column of its range, so it can’t find “Stapler” and walk left to the SKU. XLOOKUP has a separate search range and result range, so direction doesn’t matter.

Trap 1: one inserted column

Before and after inserting a column: VLOOKUP returns Bolt, XLOOKUP still returns 9.9

I inserted a Supplier column between Product and Price. Sheets updated both ranges by itself, but VLOOKUP’s 3 stayed 3.

Column 3 is now Supplier, so the “price” became Bolt. No error, no warning, just a supplier name where a number should be.

XLOOKUP pointed at the Price column itself (C2:C6 became D2:D6) and still returned 9.9.

Trap 2: forgetting FALSE

=VLOOKUP("P-105", A2:D6, 3)

VLOOKUP without FALSE returns 27.5 for a missing SKU; exact lookups return #N/A or not found

P-105 doesn’t exist. Without the fourth argument VLOOKUP does an approximate match, so it returned 27.5, the price of P-104. A made-up product, a real-looking price.

XLOOKUP is exact by default. You only get approximate matching when you ask for it.

When you do want “closest”: discount tiers

Approximate matching is useful for tiers. A Tiers tab:

Min order	Discount
0	0%
50	5%
100	10%
200	15%

=XLOOKUP(130, Tiers!A2:A5, Tiers!B2:B5, , -1)

XLOOKUP picking the 10% tier for a 130 order and Ana's latest order, Stapler

The -1 means “exact, or the next smaller value”. An order of 130 got 0.1 (10%). =VLOOKUP(130,Tiers!A2:B5,2,TRUE) also gave 0.1, but only because the tiers are sorted ascending. XLOOKUP’s -1 doesn’t require sorting.

Bonus: the latest match, not the first

An order log with Ana on Oct 1 (Notebook) and again on Oct 7 (Stapler):

=XLOOKUP("Ana", Log!B2:B5, Log!C2:C5, "", 0, -1)

The last -1 searches bottom-up, so it returned Stapler, her latest order. VLOOKUP("Ana",…,FALSE) returned Notebook, the first one.

One that gets both of them

=XLOOKUP("Notebook ", B2:B6, C2:C6, "not found")   → not found
=XLOOKUP(TRIM("Notebook "), B2:B6, C2:C6, "not found")   → 4.5

A trailing space (very common after copy-paste from email) breaks exact matching in both functions. TRIM the key, or clean the column once.

Cheat sheet

You want	Write
Exact lookup	`=XLOOKUP(key, search_col, result_col)`
Friendly miss	add `, "not found"` as 4th argument
Several columns	make the result range wider: `B2:D6`
Tiers / brackets	`, , -1` (next smaller)
Latest entry	`, "", 0, -1` (search from bottom)
Old file with VLOOKUP	always end with `, FALSE`

If a sheet is shared with Excel users on old versions, VLOOKUP with FALSE is still fine. Just don’t hard-code the column number in a sheet people add columns to.

Got a sheet where lookups keep breaking every time someone adds a column? Send me what it does, and I’ll tell you whether a formula fix is enough or it needs a small script.