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

Assume we have a list of products in cells C3 through C7 and we wish to determine where in the list the product from cell E3 is located. Here, we'll use XMATCH to determine an item's position within a list.

Example of using XMATCH to find the position of an item in a list

Syntax

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 exact position of the first phrase that exactly matches or comes closest to the value of "Gra" is determined in the example that follow.

Formula: XMATCH(E3,C3:C7,1)

An Excel table listing different fruit products from cell C3 to C7. The XMATCH formula is used to find the position in the table where the text matches "gra" (defined in cell E3). The formula returns "2" as the text "Grape" is in position two in the table.

Example 2

The number of salespeople qualified for a bonus is determined in the following example. In order to discover the closest item in the list or an exact match, this also uses 1 for the match_mode; however, because the data is numeric, it returns a count of values. Since there were four sales representatives that exceeded the bonus amount in this instance, the function yields 4.

Formula=XMATCH(F2,C3:C9,1)

An Excel table that lists Sales Representatives names in cells B3 to B9, and the total sales value for each representative in cells C3 through C9. The XMATCH formula is used to return the number of sales representatives eligible for bonuses if they meet the threshold amount set in cell F2.

Example 3

Next, we'll perform a simultaneous vertical and horizontal lookup using a mix of INDEX/XMATCH/XMATCH. In this instance, we would want the sales total for a certain sales representative and month to be returned. This is comparable to combining INDEX and MATCH methods, but it takes less arguments.

Formula=INDEX(C6:E12;XMATCH(B3,B6B12), XMATCH(C3,C5:E5))

An Excel table where sales representative names are listed in cells B6 through B12, and sales amounts for each representative from the months of January through March are listed in columns C, D and E. The formula combination of INDEX and XMATCH is used to return the sales amount of a specific sales representative and month listed in cells B3 and C3.

Example 4

In addition, XMATCH can be used to return a value within an array. =XMATCH(4,{5,4,3,2,1}), for instance, would provide 2 because 4 is the array's second entry. While =XMATCH(4.5,{5,4,3,2,1},1) produces 1 in this exact match case, the match_mode argument (1) is configured to return either 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 Communities.

See also

XLOOKUP function

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.