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.

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

Note: August 28, 2019: XMATCH is currently a beta feature, and only available to a portion of Office Insiders at this time. We'll continue to optimize it over the next several months. When XMATCH is ready, we'll release it to all Office Insiders, and Office 365 subscribers.

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

-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. This is the 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 uses a wildcard search to find the position of the first term that looks like "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 uses 1 for the match_mode to find an exact match or the next largest item in the list. In this case, the function returns 4, since there are 4 sales reps who exceeded the bonus amount. Note that this method requires your data to be sorted in descending order.

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.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

XLOOKUP function

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×