XMATCH

When Microsoft released XLOOKUP, they also released XMATCH. XMATCH hasn’t received as much attention as XLOOKUP, so I wanted to talk about it here. In summary, it is similar to the legacy MATCH function we’ve been using for decades, but it receives similar enhancements introduced with XLOOKUP. Let’s get to it.

XMATCH

Before we get into the function arguments, we should talk about what the function does. In summary, XMATCH (similar to MATCH) is designed to return the relative position of a list item. In other words, if the item we are trying to find is the first one in the list, it returns 1. If it is the second, it returns 2 … and so on. Although this capability may not sound like much on its own, it is quite handy when we nest it inside another function.

Here is a screenshot of the Function Arguments dialog:

Note: at the time I’m writing this, XLOOKUP is available in O365 w/Insiders Fast update channel. Depending on your version of Excel, you may not see it in your function list.

So, here is the function syntax:

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

Where:

• lookup_value is the value we are trying to find
• lookup_array is where we are looking for it
• [match_mode] is optional, and defaults to exact match
• [search_mode] is optional, and defaults to top-to-bottom

The first two arguments lookup_value and lookup_array work the same as the legacy MATCH function. It is the 3rd and 4th arguments where we see the enhancements:

[match_mode] defaults to 0 for exact match. The options we have are:

• 0 exact match (similar to MATCH)
• 1 exact match or next larger (similar to MATCH)
• -1 exact match or next smaller (similar to MATCH)
• 2 wildcard (supports wildcard character in lookup value like A* or A?)

[search_mode] defaults to top-to-bottom search, and the options are:

• 1 search the list from top to bottom (default)
• -1 search the list from bottom to top (find the last one in the list if multiples)
• 2 binary search – sorted in ascending order (improve performance)
• -2 binary search – sorted descending (improve performance)

This function provides another option, so depending on the workbook, XMATCH may just be the right tool for the job … thanks!