XLOOKUP vs VLOOKUP in Google Sheets
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
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Syntax | VLOOKUP(key, range, index, [is_sorted]) | XLOOKUP(key, search_range, result_range, [missing_value], [match_mode], [search_mode]) |
| Return column | Counted by a number (index) from the first column | Chosen as a separate range |
| Direction | Returns the search column or columns to its right | Any direction, including to the left |
| Default match | Approximate (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 |
| Wildcards | Work only with exact match (FALSE) | Use match_mode 2 |
| Search order | First match from the top | First 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.
| A | B | C | |
|---|---|---|---|
| 1 | ID | Name | Price |
| 2 | P-101 | Mug | 6.5 |
| 3 | P-102 | Lamp | 24 |
| 4 | P-103 | Backpack | 39 |
| 5 | P-104 | Notebook | 8.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
- The sheet already uses it and works.
- The key is always in the leftmost column and you always use
FALSE. - You share the file with someone who only knows VLOOKUP.
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.
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.
What to learn next
- XLOOKUP: look up a value in any direction.
- VLOOKUP: fetch one value from a table.
- VLOOKUP to the left: three ways to look up a value to the left of the key.
Syntax and rules in this guide were checked against Google's XLOOKUP vs VLOOKUP help page. See all guides.