Google Sheets function
XLOOKUP function in Google Sheets
XLOOKUP in Google Sheets finds a search key in one row or column and returns the value at the same position from another range. Exact match is the default. Optional settings provide a missing value, alternate match modes, and forward, reverse, or binary search modes.
Source: Google Docs Editors HelpLast verified Nov 5, 2026
Syntax
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])Arguments
| Argument | Required | What it means |
|---|---|---|
search_key | Yes | The value to search for. |
lookup_range | Yes | The single row or column to search. |
result_range | Yes | The range containing the value or values to return. |
missing_value | No | The value returned when no match is found. #N/A is the default. |
match_mode | No | The exact, next-larger, next-smaller, or wildcard match behavior. |
search_mode | No | The direction or binary method used to search the lookup range. |
Examples
Find a product price
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Search | |
| 2 | Notebook | 6 | Pen | |
| 3 | Pen | 2 |
=XLOOKUP(D2,A2:A3,B2:B3,"Not found")Result: 2
XLOOKUP finds Pen in A2:A3 and returns the value at the same position in B2:B3.
Common mistakes
Using lookup and result ranges with different lengths.
Use ranges with matching row or column sizes.
Using binary search on an unsorted range.
Sort ascending for search mode 2 or descending for search mode -2.