Excel XLOOKUP and XMATCH

(Originally posted August 13, 2024 By ~ Jake Armstrong)

Hello, Microsoft 365 Insiders! I’m Jake Armstrong, a Product Manager on the Excel team. As mentioned in this post, I’m excited to announce the release of Regex mode for XLOOKUP and XMATCH. Previous Post.

memo: These new feature modes are preview features. The results may change significantly before they are released widely based on your feedback. We recommend that you do not use these features in your critical workbooks until they are generally available.

New regex mode for XLOOKUP and XMATCH

You can now leverage regular expressions within your existing XLOOKUP and XMATCH functions using the new XLOOKUP and XMATCH functions. [match_mode] = 3 and use a regular expression pattern with lookup_value.

Regular expression pattern as lookup_value

This allows you to use XLOOKUP and XMATCH to match part of the text in a cell or other text patterns that can be described by regular expressions.

For example, let’s say you have complex data where the United States is listed as “USA” and “United States”, and you want to match based on which one appears first.

We will use XLOOKUP with “USA|United States” as lookup_value and match_mode = 3.

=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)

XLOOKUP function using regular expression mode

Instead, you can use XMATCH to return the position of the match.

=XMATCH(lookup_value,lookup_array,match_mode,search_mode)

XMATCH function using regular expression mode

Tips and Tricks

• When writing regular expression patterns, you can use symbols called ‘tokens’ that match a variety of characters. Some useful tokens to get you started include:
  • “[0-9]”: number
  • “[a-z]”: characters in the range a~z
  • “.”: all characters
  • “a” : “a” character
  • “a*”: 0 or more “a”s
  • “a+” : one or more “a”
• Ask me Bing Copilot For regular expression patterns!

effectiveness

This feature is rolling out to Beta Channel users running:

• Windows: Version 2408 (Build 17931.20000)
• Mac: Version 16.89 (Build 24080715)

Not yet? It’s probably us, not you.

Features are released over time to ensure everything works smoothly. They are released slowly to a larger number of Insiders, highlighting features you may not have. Sometimes we remove elements based on your feedback to make them even better. This is rare, but we also have the option to completely remove a feature from the product, even if you, as an Insider, had the opportunity to try it out.

feedback

We want to hear from you! Click here help > feedback Please use Excel to submit your thoughts on new features.

Learn about Microsoft 365 Insider Program And join us Microsoft 365 Insider Newsletter Get the latest Insider features straight to your inbox once a month!