How to understand the XMATCH() function in Excel

The XMATCH() function, introduced in Excel in September 2019, is a powerful tool designed to replace and enhance the traditional MATCH() function. This post will explore a few detailed examples that highlight its unique functionalities.

You can follow along with the exercise file below:

XMATCH() syntax

The syntax for XMATCH() is as follows:

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

Let’s explore how this syntax works now:

1. lookup_value: This is the value you want to find in the lookup_array. It can be a number, text, logical value, or a cell reference.
2. lookup_array: This is the array or range of cells that contains possible matches to the lookup_value.
3. match_mode (optional): This argument specifies how Excel matches the lookup_value with values in the lookup_array. It can take the following values:
   • 0 or omitted: Exact match. If no exact match is found, an error is returned.
   • -1: Exact match, or next smallest item if no exact match.
   • 1: Exact match, or next larger item if no exact match.
   • 2: A wildcard match where ? matches any single character and * matches any sequence of characters.
4. search_mode (optional): This argument specifies the manner in which the search is conducted. It can take the following values:
   • 1 or omitted: Search from first to last.
   • -1: Search from last to first.
   • 2: Binary search that assumes the lookup_array is sorted in ascending order. Faster on large arrays.
   • -2: Binary search that assumes the lookup_array is sorted in descending order.

Example 1: Finding the first occurence

To get started, let’s try to find the relative position of the first occurrence of “IT” in the deparment column of the dataset. This will look remarkably similar to MATCH(), with the added benefit of a more intuitive default set of matching rules: by default, an exact match is returned which is different than what MATCH() does.

=XMATCH("IT", hire_dates[department])

The MATCH() function, when used without specifying the match type argument, defaults to an approximate match (1). This default setting is geared towards finding the largest value that is less than or equal to the lookup value, under the assumption that the data is sorted in ascending order.

In contrast, XMATCH() defaults to an exact match (0) when the match type argument is omitted. This means XMATCH() searches for a value that exactly equals the lookup value, regardless of the data’s order. This default behavior of XMATCH() not only provides a more intuitive approach when an exact match is needed but also reduces the likelihood of errors in scenarios where the exactness of the match is crucial and the data sorting cannot be guaranteed.

Example 2: Finding the last occurence of a value

Another exciting feature of XMATCH() is its ability to set sort order. For example, if you were trying to locate the last employee in the “Sales” department, XMATCH() can perform this lookup by searching backwards—a functionality that MATCH() lacks:

=XMATCH(E3, hire_dates[department], , -1)

Example 3: Find the last sale before a given date

Finally, let’s consider an example where we’d like to find the last employee hired before a given date. Here, XMATCH can be employed with its unique backward search capability:

=XMATCH(E4, hire_dates[hire_date], -1, -1)

This setup uses -1 in the match type to look for the closest value without going over, and another -1 for the search mode to look from the bottom up. This ability to search backwards is a significant advantage over MATCH, which does not support this feature, enhancing the functionality for chronological data analysis.

Conclusion

The XMATCH() function provides enhanced flexibility and capabilities compared to the traditional MATCH() function, making it an invaluable addition to your Excel toolkit. It introduces additional search modes, such as finding the last occurrence or the next larger item, which simplifies complex data analysis tasks.

The examples presented here showcase some of the unique applications of XMATCH() that were not feasible with MATCH(). Here are a few more:

• Exact or Next Smaller Item: XMATCH() can determine the position of an exact match or return the next smaller item if an exact match isn’t found. This feature is especially useful in financial scenarios, such as identifying an interest rate that is closest to, but does not exceed, a specified value.
• Wildcard Matching: Unlike MATCH(), XMATCH() supports the use of wildcard characters like * or ? in lookup values. This capability is beneficial for partial text matches, such as locating a product code that begins with specific letters or finding a name that includes a particular character pattern.
• Custom Binary Search: XMATCH() allows you to conduct a binary search to quickly locate an item in a sorted array, greatly enhancing performance over the linear search methods used by MATCH() in unsorted arrays.

For more information on how XMATCH() works and to view more examples, head to Microsoft’s documentation.

If you’re interested in learning more about how dynamic array formulas function and want to explore other dynamic array functions, consider checking out my book, Modern Data Analytics in Excel: