The MATCH function returns the relative position for a value in the data range you specified.
Formula:
= MATCH(lookup_value, lookup_array, [match_type])
Explanation:
Lookup_value is required, the value to search for.
Lookup_array is required, the data range for the search.
Match_type is optional, a number (1,0 or -1) to specify the match style. The default is 1 when it is omitted.
– When it is 1 or omitted, returns the largest value less than or equal to the lookup value; has to be ascending order.
– When it is 0, returns the first value that is exactly the same as lookup value;
– When it is -1, returns the smallest value greater than or equal to the lookup value, has to be descending order.
Example 1:
=MATCH(61900,C2:C6,1)
Where match_style is 1, to look for the largest value that is less than or equal to 61900 in data array C2:C6 which is sorted in ascending order. It is 61877 and in the position of 2.
Example 2:
=MATCH(61900,D2:D6,-1)
Where match_style is -1, to look for the smallest value that is greater than or equal to 61900 in data array D2:D6 which is sorted in descending order. It is 61877 and in the position of 4.
Example 3:
=MATCH(61877,C2:C6,0)
Where match_style is 0, to look for the first value that is exact the same as 61877 in data array C2:C6 and returns 2.
Example 4:
=MATCH(61877,D2:D6,0)
Where match_style is 0, to look for the first value that is exact the same as 61877 in data array D2:D6 and returns 4.