Lookup & Reference
Modern
MATCH
Returns the position of a value within a range.
Available in All versions
Syntax
MATCH(lookup_value, lookup_array, [match_type])What it returns
The relative position of the value, as a number.
Arguments
| Argument | Required | Description |
|---|---|---|
lookup_value |
Yes | The value to find. |
lookup_array |
Yes | The range to search in. |
match_type |
No | 1 largest <= value (default), 0 exact, -1 smallest >= value. |
Example
Lookup value
Apple
| A | |
|---|---|
| 1 | Fruit |
| 2 | Apple |
| 3 | Banana |
| 4 | Cherry |
Position
1
Find Apple, return its position (1).
=MATCH("Apple", A2:A10, 0)Important to know
match_type 1 requires ascending sort; -1 requires descending; 0 needs none.
Use 0 for exact lookups.