MATCH Function: syntax, examples, mistakes and translation notes
Returns the relative position of a value.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])
Arguments
lookup_value
Value to find.
lookup_array
One row or one column to search.
match_type
0 exact, 1 approximate ascending, -1 approximate descending in classic MATCH.
Worked examples
=MATCH(E2,A2:A100,0)
Returns E2’s position within A2:A100 using exact match.
=INDEX(C2:C100,MATCH(E2,A2:A100,0))
Uses MATCH to choose the row returned by INDEX.
Practical tips
- Use 0 for exact matching unless approximate matching is intentional.
- MATCH returns a position, not the matched value itself.
- XMATCH offers more flexible match and search modes in newer Excel versions.
Common mistakes
- Expecting MATCH to return the cell contents.
- Using approximate match on unsorted data.
Related functions
Frequently asked questions
What does MATCH do?
Returns the relative position of a value.
Why might a MATCH formula return an unexpected result?
Expecting MATCH to return the cell contents. Start by testing the smallest referenced range or condition independently before adding wrappers or fallbacks.
Check the complete formula
Function syntax is only one part of a working workbook. Paste the finished expression into the Formula Checker for structural warnings, the Formatter for readability or the Translator when a supported locale mapping is involved.
Testing note: examples are educational. Spreadsheet versions, locale settings, tables, named ranges and workbook data can change behavior, so validate important formulas with representative data.