MATCH()
Returns the relative position of a value within a range, rather than the value itself.
MATCH(lookup_value, lookup_array, [match_type])MATCH tells you where a value sits in a range, counting from 1 — not the value at that position. That position is exactly what INDEX needs as its row_num, which is why the two are almost always used together.
match_type 0 finds an exact match in any order; 1 (the default) finds the largest value less than or equal to lookup_value in data sorted ascending; -1 does the mirror image on data sorted descending.
Arguments
- lookup_value
- The value to find.
- lookup_array
- The range to search — a single row or column.
- match_typeoptional
- 0 for exact match, 1 for largest ≤ (ascending data), -1 for smallest ≥ (descending data).
Examples
=MATCH("March", A1:A12, 0)→3
March is the 3rd item in the range — an exact match.
=INDEX(B1:B12, MATCH("March", A1:A12, 0))→March's value from column B
The classic INDEX/MATCH pairing.