The XMATCH function
Returns the position of a value in a range, like MATCH but with exact match as the default and search from the end.
Syntax
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])- lookup_value
- The value to find.
- lookup_array
- The range to search.
- 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
=XMATCH(1"Plum",2A2:A4)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 3 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: Find the position of 1Plum in 2the fruit list in A2:A4.
D1=XMATCH(6,B2:B4)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 2 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |