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!

 

Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

1 comment:

  1. Laurie Garcia
    Reply

    This looks too cool along with the XLOOKUP. I just wish my version of Excel had that function. I really enjoy all of your articles and webinars. You make it easy to grasp the concepts. I wish I could afford some of the classes you offer, but it is not in my budget at this time. In the meantime, I enjoy the articles and often forward to others in my organization.

Leave a Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.