MS Excel XMATCH Formula

The XMATCH function searches for a specified item in an array or range of cells, and then returns the item’s relative position.

XMATCH Formula

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])  

ArgumentDescription
lookup_valueThe lookup value
lookup_arrayThe array or range to search
[match_mode]0 – Exact match (default)
-1 – Exact match or next smallest item
1 – Exact match or next largest item
2 – A wildcard match where *, ?, and ~ have special meaning.
[search_mode]1 – Search first-to-last (default)
-1 – Search last-to-first (reverse search).
2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.  
-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
Example 1
The following example finds the position of the first term that is an exact match or the next largest value for (i.e., starts with) “Gra”.
Example of using XMATCH to return a wildcard search
Example 2
This next example finds the number of sales people eligible for a bonus. This also uses 1 for the match_mode to find an exact match or the next largest item in the list, but since the data is numeric it returns a count of values. In this case, the function returns 4, since there are 4 sales reps who exceeded the bonus amount.
Example of using XMATCH to find the number of values above a certain limit by looking for an exact match or the next largest item
Example 3
Next, we’ll use a combination of INDEX/XMATCH/XMATCH to perform a simultaneous vertical and horizontal lookup. In this case, we want to return the sales amount for a given sales rep and a given month. This is similar to using the INDEX and MATCH functions in conjunction, except that it requires fewer arguments.
Image of using a combination of INDEX/XMATCH/XMATCH to perform a simultaneous vertical and horizontal lookup.
Example 4
You can also use XMATCH to return a value in an array. For example, =XMATCH(4,{5,4,3,2,1}) would return 2, since 4 is the second item in the array. This is an exact match scenario, whereas =XMATCH(4.5,{5,4,3,2,1},1) returns 1, as the match_mode argument (1) is set to return an exact match or the next largest item, which is 5.