XLOOKUP vs VLOOKUP in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner to intermediate · 7 min read · includes 2 free exercises

In short: Use XLOOKUP for new work. It matches exactly by default, looks in any direction, and has a built-in "not found" argument. VLOOKUP still works and is common in older sheets, but it defaults to an approximate match and cannot return columns to the left of the key.

The short answer

Both functions find a value in one place and return a related value from another. XLOOKUP is the newer one and removes several traps in VLOOKUP. If you are writing a new formula and XLOOKUP is available in your Sheets, use it. If you are reading or maintaining an old sheet, you will still meet VLOOKUP, so it helps to know both.

Side-by-side comparison

VLOOKUPXLOOKUP
SyntaxVLOOKUP(key, range, index, [is_sorted])XLOOKUP(key, search_range, result_range, [missing_value], [match_mode], [search_mode])
Return columnCounted by a number (index) from the first columnChosen as a separate range
DirectionReturns the search column or columns to its rightAny direction, including to the left
Default matchApproximate (is_sorted defaults to TRUE)Exact (match_mode defaults to 0)
If nothing matches#N/A (wrap in IFNA to customize)#N/A by default, or the missing_value you set
WildcardsWork only with exact match (FALSE)Use match_mode 2
Search orderFirst match from the topFirst to last by default, or last to first with search_mode -1

The facts in this table come from Google's help pages for VLOOKUP and XLOOKUP. One caution: the XLOOKUP page's introduction says a missing match "returns the closest match", while the missing_value description gives the default as #N/A. The two statements do not agree, so test XLOOKUP on your own data before relying on either.

Convert VLOOKUP to XLOOKUP

Take the product table in cells A1:C5.

ABC
1IDNamePrice
2P-101Mug6.5
3P-102Lamp24
4P-103Backpack39
5P-104Notebook8.75

This VLOOKUP returns the price of P-104:

=VLOOKUP("P-104", A2:C5, 3, FALSE)

The same lookup in XLOOKUP names the search column and the result column directly:

=XLOOKUP("P-104", A2:A5, C2:C5)

Both return 8.75. There is no index number to count, and no FALSE to remember, because exact match is the default.

A built-in "not found" message

VLOOKUP needs a wrapper such as IFNA to replace an error. XLOOKUP has an argument for it:

=XLOOKUP("P-999", A2:A5, C2:C5, "Not found")

This returns Not found, because P-999 is not in the table.

When VLOOKUP is still fine

For something that neither handles, such as returning every match, see FILTER.

Try it yourself

Use the product table above (A1:C5). Type your formula into the box, starting with =, then press Check.

Exercise 1: Convert a lookup

Rewrite =VLOOKUP("P-104", A2:C5, 3, FALSE) as an XLOOKUP that returns the same price.

Hint: search A2:A5, return C2:C5.

Exercise 2: Not found message

Write an XLOOKUP that looks up "P-999" in A2:A5, returns from C2:C5, and shows Not found when there is no match.

Hint: the fourth argument is missing_value.

What to learn next

Syntax and rules in this guide were checked against Google's XLOOKUP vs VLOOKUP help page. See all guides.