MATCH tells you where something is in a list. It does not return the value, only its position, for example that Priya is the 14th name. On its own this looks small, but it is the engine behind INDEX MATCH and many dynamic formulas.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])
- 0: exact match. Use this almost always.
- 1: finds the largest value less than or equal to the lookup value. The list must be sorted ascending.
- -1: finds the smallest value greater than or equal. The list must be sorted descending.
Simple example
Names in A2:A100. To find where Priya is:
=MATCH("Priya", A2:A100, 0)
If she is in A15, the answer is 14, because the range starts at A2.
With INDEX to return a value
=INDEX(C2:C100, MATCH(F1, A2:A100, 0))
MATCH finds the row, and INDEX returns the value from that row of column C.
Pick the column by header name
Instead of a fixed column number, match the header: =INDEX(A2:F100, MATCH(F1,A2:A100,0), MATCH(G1,A1:F1,0)). Now the report keeps working if someone inserts a column.
Check whether a value exists
=ISNUMBER(MATCH(A2, Other!A:A, 0)) returns TRUE if A2 is in the other list. This is a quick way to compare two lists.
Common mistakes
- Leaving the last argument out. The default is 1, which needs sorted data and can return wrong answers.
- #N/A means the value was not found, often because of extra spaces or text numbers.