XLOOKUP in Google Sheets
Finds a value in one range and returns the matching item from another. Exact match by default, with an optional value for not found.
In Google Sheets
Google Sheets added XLOOKUP in 2022 with the same arguments in the same order, including the value for when nothing is found, match mode and search mode.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value
- The value to find.
- lookup_array
- The range to search.
- return_array
- The range to return from, the same size as lookup_array.
- if_not_found (optional)
- What to return when there is no match, instead of #N/A.
- match_mode (optional)
- 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcards.
- search_mode (optional)
- 1 first to last (default), -1 last to first.
Examples
D1
=XLOOKUP(1"Plum",2A2:A4,3B2:B4)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 5 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: Find 1Plum in 2the fruit in A2:A4 and return 3its price from B2:B4.
D1=XLOOKUP("Kiwi",A2:A4,B2:B4,"Not found")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | Not found | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |