What it does
MATCH returns the position of a value inside a range.
Syntax or pattern
=MATCH(lookup_value, lookup_array, [match_type])✅
5 practical examples
1
Find a product position
Return where a product appears in a list.
=MATCH(A2, Products[Product], 0)The result is a number showing the item position.
2
Find a month column
Return the column number for a month header.
=MATCH(H1, Sales[#Headers], 0)Useful inside INDEX or other formulas.
3
Check if a value exists
Use ISNUMBER with MATCH to test if an item is in a list.
=ISNUMBER(MATCH(A2, ProductList, 0))TRUE means the item was found.
4
Find a row for INDEX
Use MATCH as the row argument inside INDEX.
=INDEX(Prices, MATCH(A2, Products, 0))This is the classic INDEX MATCH pattern.
5
Approximate position in sorted values
Find where a value fits into sorted thresholds.
=MATCH(B2, Thresholds, 1)Use this only when thresholds are sorted ascending.
Common mistakes to avoid
- Forgetting exact match mode.
- Expecting MATCH to return the value instead of the position.
- Using approximate match on unsorted data.
Related Excel examples
FAQ
What does MATCH return?
It returns the relative position of the match, not the matched value.