How to Use XLOOKUP in Google Sheets
What XLOOKUP does
Like VLOOKUP, XLOOKUP takes a value you have, finds it in a list, and returns related information from the same row. The difference is that you point to the column you search and the column you want back as two separate ranges, so you never count columns.
XLOOKUP syntax
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
search_key: the value you are looking for, such as"P-102"or a cell likeE2.lookup_range: the single column (or row) to search in.result_range: the column (or row) to return a value from. It must be the same size as the lookup range.missing_value(optional): what to return if nothing matches, such as"Not found". Without it you get#N/A.match_mode(optional):0exact match (the default),-1exact or the next smaller item,1exact or the next larger item,2wildcard match.search_mode(optional):1search from the first item (the default),-1search from the last item.
A step-by-step example
Here is the same product table used in the VLOOKUP guide, in cells A1:C5.
| A | B | C | |
|---|---|---|---|
| 1 | ID | Product | Price |
| 2 | P-101 | Notebook | 4.50 |
| 3 | P-102 | Pen | 1.20 |
| 4 | P-103 | Backpack | 32.00 |
| 5 | P-104 | Stapler | 8.75 |
To find the price of P-102:
=XLOOKUP("P-102", A2:A5, C2:C5)
This returns 1.20. Here is why:
- Sheets searches
A2:A5for"P-102". - It finds it in the second position of that range.
- It returns the second item of
C2:C5, which is the price.
To get the product name instead, change the result range to B2:B5. You do not need an index number.
Look up to the left
VLOOKUP can only return columns to the right of the search column. XLOOKUP has no such limit. To find the ID of the product named Backpack:
=XLOOKUP("Backpack", B2:B5, A2:A5)
This returns P-103, even though the ID column is to the left of the product column.
Handle missing values
If the search key is not in the list, XLOOKUP returns #N/A. The fourth argument lets you show something friendlier without wrapping the formula in IFERROR:
=XLOOKUP("P-999", A2:A5, C2:C5, "Not found")
This returns Not found.
XLOOKUP vs. VLOOKUP
- No column counting. VLOOKUP needs an index number, which breaks if you insert a column. XLOOKUP points at the result column directly.
- Exact match by default. VLOOKUP defaults to approximate matching unless you type
FALSE. - Works in any direction. XLOOKUP can return a column to the left of the lookup column.
- Built-in missing value. The fourth argument replaces many IFERROR wrappers.
If you already use VLOOKUP and it works, there is no need to rewrite old sheets. For new formulas, XLOOKUP is usually the simpler choice. See the VLOOKUP guide for the older approach.
Common XLOOKUP errors
#N/A: "Did not find value"
- The value is not in the lookup range. Check for typos.
- There are hidden spaces. Wrap your search key in
TRIM(). - One side is a number and the other is text. For example,
101and"101"do not match.
Ranges of different sizes
The lookup range and the result range must have the same number of rows (or columns). A2:A5 with C2:C4 returns an error.
Try it yourself
Use the product table above (A1:C5). Type your formula into the box, starting with =, then press Check.
Exercise 1: Find a price
Write a formula that returns the price of product P-104, searching the IDs in column A.
Exercise 2: Look to the left
Write a formula that returns the ID of the product named Backpack, searching the names in column B.
What to learn next
- VLOOKUP: the classic lookup, still common in shared sheets.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- FILTER: return every matching row, not just one.
- INDEX + MATCH: another way to look in any direction.
- SUMIF: add up the rows that match a condition.
- QUERY: when you want to filter and summarize many rows, not just fetch one value.
Syntax and behavior in this guide were checked against Google's XLOOKUP help page. See all guides.