The MATCH function
MATCH does not return a value, but its position: "Smith is on the 42nd row of the range". It is half of INDEX MATCH, the lookup that works in every Excel version.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])
In a French Excel: =EQUIV(valeur_cherchée; tableau_recherche; [type]).
Arguments
- lookup_value: what you are looking for.
- lookup_array: a single row or a single column.
- match_type: 0 for an exact lookup. Omitted or 1: the largest value less than or equal, in a range sorted in ascending order; -1: the reverse, in a range sorted in descending order.
An example
=MATCH("March",A1:L1,0)
Returns 3 if "March" is in the 3rd cell of A1:L1.
Pitfalls
- Forgetting the 0: without it, the lookup is approximate, and the result wrong if the range is not sorted.
- A missing value returns #N/A.
Going further
- INDEX, to return the value at that position.
- Explain a formula: flags a MATCH without 0.
Frequently asked questions
- Is MATCH case-sensitive?
- No: "SMITH" and "Smith" are the same value for MATCH.