XMATCH Function
Finds the position of a value — the modern upgrade to MATCH.
Category
Lookup & Reference
Available in
Microsoft 365, Excel 2021+
Returns
A position number
Overview
XMATCH returns where a value sits in a list, like MATCH, but defaults to an exact match and can search from the bottom up.
Pair it with INDEX for a flexible lookup that works in any direction.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| lookup_value | Yes | What to find. |
| lookup_array | Yes | The row or column to search. |
| match_mode | Optional | 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard. |
| search_mode | Optional | 1 = first to last (default), -1 = last to first, 2/-2 = binary search. |
Examples
Example 1 — Position in a list
Where is Dev?
C2fx=XMATCH("Dev", A2:A5)
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Position | |
| 2 | Ana | 4 | |
| 3 | Ben | ||
| 4 | Cara | ||
| 5 | Dev |
=XMATCH("Dev", A2:A5) Explain
Example 2 — Two-way lookup with INDEX
Find a rep's sales for a chosen month.
=INDEX(B2:M20, XMATCH(P1, A2:A20), XMATCH(P2, B1:M1)) Explain
Use search_mode -1 to find the last occurrence: =XMATCH("Ana", A2:A500, 0, -1).
Common mistakes
- #N/A errors. The value isn't in the list — check for spaces or numbers stored as text.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.