The MATCH function
Returns the position of a value in a row or column. Use 0 as match_type for an exact match.
Syntax
MATCH(lookup_value, lookup_array, [match_type])- lookup_value
- The value to find.
- lookup_array
- A single row or column to search.
- match_type (optional)
- 0 exact match; 1 (default) largest value less than or equal, in ascending data; -1 smallest value greater than or equal, in descending data.
Examples
D1
=MATCH(1"Pear",2A2:A4,30)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 2 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: Find the position of 1Pear in 2the fruit list in A2:A4, 3only on an exact match.
D1=MATCH(5,B2:B4,0)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 3 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |