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.


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])


  • 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!

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

1 Comment

  1. Laurie Garcia on October 24, 2019 at 7:36 am

    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 Comment