Searches within a range of cells, and if there is a matching cell, returns the position of the cell relative to the top-left corner of the range of cells searched for.
It is rarely used alone, but is often used in combination with the INDEX or OFFSET functions.
How it works
=MATCH(lookup_value, lookup_array, match_type)
Cell range to search.
|match_type||1||Specify the search method.|
1: Maximum below the lookup_value. lookup_array must be sorted in ascending order.
0: Exact match.
-1: Minimum value above the lookup_value. lookup_array must be sorted in descending order.
Here is an example of the results by match_type.
The blue table is the lookup_array and the green table is the lookup_value and function results.
match_type: 1. Maximum below the lookup_value.
First, the lookup_table must be arranged in ascending order.
In the example, 1 through 19 correspond to the top of the lookup_table , so they get 1, and Anything above 80 will get 5.
Anything less than 0 will result in a #N/A error as there is no corresponding item.
match_type: 0. Exact match.
0 specification does not require the lookup_table
to be reordered.
In this example, if there is an exact match, the position is obtained.
match_type:-1. Minimum above the lookup_value.
First, the lookup_table must be sorted in descending order.
In this example, if 61 to 80 corresponds to the top of the lookup_table, we get 1.
Anything less than 0 will get 5.
Anything greater than 80 will result in a #N/A error as there is no corresponding item.
Relative positioning can be applied to horizontal lookup_table as well.