Excel MATCH: Find a Position in a List
By Szabó Gergő · Updated
MATCH returns a position, not a value. Feed that position to INDEX when you want the matching cell.
Syntax and arguments
=MATCH(lookup_value, lookup_array, [match_type])- lookup_value
- The value to find.
- lookup_array
- A single row or column.
- match_type
- 0 exact, 1 approximate ascending, -1 descending.
MATCH examples
01
Exact position
Codes in A2:A100, target in G2.
=MATCH(G2,A2:A100,0)0 is exact match. Always use 0 for IDs.
02
With INDEX
Return column C.
=INDEX(C2:C100,MATCH(G2,A2:A100,0))This is the classic left-or-right lookup.
03
XMATCH last
Microsoft 365, newest match.
=XMATCH(G2,A2:A100,0,-1)Search mode -1 starts at the bottom.
Common mistakes
Omitting 0 for IDs
Default match_type is 1, which needs a sorted list and can return the wrong row.
Two-column lookup_array
MATCH needs one row or one column.
Expecting a cell value
MATCH returns 3, not the text in that cell. Wrap INDEX.
MATCH FAQ
MATCH vs XMATCH?
XMATCH defaults to exact match and can search last-to-first. Prefer it on Microsoft 365.
Why #N/A?
No exact match, or data types differ. Try TRIM and VALUE.
Can I match a wildcard?
Yes with match_type 0 and * or ? in the lookup value.