Search for a value in a data set.
Syntax:
MATCH(lookup_key_value, lookup_data:Array|Range, lookup_type:Number)
Looks up the lookup_key_value in the lookup_data array (or range of cells).
If lookup_data specifies an array,
MATCH
returns the position in that array.
If lookup_data specifies a range,
MATCH
returns the cell reference containing the value matched.
The lookup process is controlled by lookup_type:
lookup_type | MATCH action |
---|---|
1 |
MATCH finds the largest value which is less than or equal to lookup_key_value.
NB lookup_data must be sorted in ascending order.
|
0 |
MATCH finds the value which exactly matches lookup_key_value. |
-1 |
MATCH finds the smallest value which is greater than or equal to lookup_key_value.
NB lookup_data must be sorted in descending order.
|
Example:
Function | Result | |
---|---|---|
MATCH(2.5, {1,2,3}, 1) |
2 |