How to Use XMATCH in Excel: Lookup Position
By Szabó Gergő · Updated
XMATCH returns the relative position of a lookup value in a row or column. Pair it with INDEX when you need a flexible two-way lookup without VLOOKUP column numbers.
Syntax and arguments
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])- lookup_value
- The value to find.
- lookup_array
- The single row or column to search.
- match_mode
- 0 exact, -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
- search_mode
- 1 first-to-last, -1 last-to-first, 2 or -2 binary search.
XMATCH examples
IDs are in A2:A200 and G2 is the ID.
=XMATCH(G2,A2:A200,0)The result is 1 for the first row of the lookup array, not the worksheet row.
Months repeat in B2:B500.
=XMATCH(F2,B2:B500,0,-1)Search mode -1 finds the last exact month.
Names in A, emails in D, lookup in G2.
=INDEX(D2:D200,XMATCH(G2,A2:A200,0))INDEX returns the email from the matched position.
Common mistakes
Treating the result as a worksheet row
Add the start row minus 1, or use INDEX instead of ROW arithmetic.
Forgetting exact match
Pass 0 for match_mode on IDs and names.
Lookup array is two-dimensional
XMATCH needs a single row or column. Use XLOOKUP for a return array.
XMATCH FAQ
When should I use XMATCH instead of XLOOKUP?
Use XMATCH when you need a position, a MATCH replacement, or INDEX pairing. Use XLOOKUP when you only need the returned value.
Does XMATCH work with wildcards?
Yes, with match_mode 2 and * or ? in the lookup value.
What does #N/A mean?
No match. Check data types, spaces, and match_mode.