Quick Navigation
XMATCH Function
Summary
The XMATCH function returns the relative position of a specified item within a range or array, offering enhanced flexibility over the traditional MATCH function with advanced search and match options.
Syntax
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| lookup_value | Any |
Yes | Value to locate within the lookup array |
| lookup_array | Range/Array |
Yes | Array or cell range containing the data to search |
| match_mode | Number |
No | Match type: 0 (exact), -1 (next smaller), 1 (next larger), 2 (wildcard) |
| search_mode | Number |
No | Search direction: 1 (first-to-last), -1 (last-to-first), 2/ -2 (binary search) |
Using the XMATCH Function
XMATCH provides powerful position lookup capabilities with greater control than MATCH. Use it to find exact positions, count qualifying items, perform 2D lookups with INDEX, or leverage binary search on sorted data for optimal performance.
Common XMATCH Examples
Find Exact Position in Product List
=XMATCH(E3,C3:C7)Returns position (2) of 'Grape' in fruit list C3:C7 (exact match)
Count Items Meeting Criteria
=XMATCH(F2,C3:C9,1)Returns 4 - count of sales reps exceeding bonus threshold F2
2D Lookup with INDEX/XMATCH
=INDEX(C6:E12,XMATCH(B3,B6:B12),XMATCH(C3,C5:E5))Returns sales for specific rep (B3) and month (C3)
Array Position Lookup
=XMATCH(4,{5,4,3,2,1})Returns 2 (position of 4 in array)
Frequently Asked Questions
Common Errors and Solutions
#N/A - Value not found
Cause: lookup_value doesn't exist in lookup_array with exact match
Solution: Use match_mode 1 or -1 for approximate matching, or verify data
Incorrect results with binary search
Cause: lookup_array not sorted properly
Solution: Sort data ascending (mode 2) or descending (mode -2) first
#VALUE! error
Cause: Invalid match_mode or search_mode values
Solution: Use only valid numeric codes: match_mode (0,-1,1,2), search_mode (1,-1,2,-2)
Notes
- Default behavior is exact match, first-to-last search
- Binary search requires properly sorted data or returns wrong results
- Wildcard characters (*,?,~) work with match_mode=2
- Position returned is always relative to start of lookup_array
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021