The XMATCH function executes lookup and returns a position in vertical or horizontal ranges, it searches for a specified item in an array or range of cells and then returns the item’s relative position. XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches.
Purpose of Excel XMATCH Function
Searches for a specified item in an array or range of cells.
Return value
Returns the relative position of the specified.
Syntax
=XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])
Arguments
| Arguments | Description | Remarks |
| Lookup Value | Refer a value to be looked up | Mandatory |
| Lookup Array | Array or Range to search | Mandatory |
| Match Mode | 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match. | Optional |
| Search Mode | 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending. | Optional |
Usage notes
- XMATCH can perform lookups in vertical or horizontal ranges, and is meant to be a more flexible and powerful successor to the MATCH function.
Availability
Microsoft 365