Site icon ExcelNotes

How to Use MATCH Function

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.

Download: MATCH Function

Exit mobile version