MATCH Function. Obtains the relative position of a cell as a result of a search.(Microsoft Excel)
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)
| Name | Omission | Explanation |
|---|---|---|
| lookup_value | Required argument. Search value. | |
| lookup_array | Required argument. 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. |
Demonstrate
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.
Lateral use
Relative positioning can be applied to horizontal lookup_table as well.
---