XMATCH returns the position of a value inside a row or column. It is the newer version of MATCH, with safer exact-match defaults, wildcard support, and the ability to search from the end of a list.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel for the web. XMATCH is not available in Excel 2019, Excel 2016, or older versions.
Write a basic XMATCH
XMATCH returns a position number, not the matching value itself.
- Click the result cell.
- Enter =XMATCH(.
- Add the value to search for, such as “Chicago”.
- Add the range to search, such as A2:A20.
- Close the formula and press Enter.
=XMATCH(“Chicago”,A2:A20) returns the position of Chicago inside that range. If it is the seventh item, the result is 7.
Use match and search options
Optional arguments control matching and direction.
- Use the syntax =XMATCH(lookup_value,lookup_array,match_mode,search_mode).
- Use 0 for exact match, which is also the default.
- Use 2 for wildcard matching with * and ?.
- Use -1 or 1 for exact-or-next-smaller or exact-or-next-larger matches.
- Use search mode -1 to search from last to first.
- Use binary search modes only on sorted data.
A reverse-search example is =XMATCH(“Chicago”,A2:A20,0,-1), which finds the last matching item instead of the first.
More control
XMATCH versus MATCH
MATCH defaults to approximate matching unless told otherwise. XMATCH defaults to exact matching, which is safer for text, IDs, and unsorted lists.
Return a related value with INDEX
Use =INDEX(B2:B20,XMATCH(“Chicago”,A2:A20)) to return the value in column B at the position where Chicago appears in column A.
Fix #N/A
Check spelling, extra spaces, and hidden characters. If partial matching is intended, use wildcard match mode and a pattern such as “Ch*”.
Sources
- Microsoft Support – XMATCH function (2025)
- Microsoft Support – MATCH function (2025)
- Microsoft Support – XLOOKUP function (2025)