Xmatch function

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

Here we'll use XMATCH to find the position of an item in a list.

The XMATCH function returns the relative position of an item in an array or range of cells. 

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

Argument	Description
lookup_value Required	The lookup value
lookup_array Required	The array or range to search
[match_mode] Optional	Specify the match type: 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] Optional	Specify the search type: 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.

Examples

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 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 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.

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. 

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

XLOOKUP function