Search for a value in a data set.
Syntax:
MATCH( lookup_key_value, lookup_data:Range or Array, lookup_type:Number )
Looks up the lookup_key_value in the lookup_data range of cells (or array).
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 (largest values at the bottom). |
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 (largest values at the top). |
Example:
Function | Result | |
---|---|---|
MATCH(2.5, {1,2,3}, 1) |
2 |