MATCH Function. Obtains the relative position of a cell as a result of a search.(Microsoft Excel)

03/26/2024

Japanese version.

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)
NameOmissionExplanation
lookup_valueRequired argument.
Search value.
lookup_arrayRequired argument.
Cell range to search.
match_type1Specify 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.

Spill

If you specify the argument "lookup_value" the cell range, it will be Spil.

---

Links

Microsoft Excel Functions Lookup & Reference