Regex lookups with XLOOKUP

Hello, and welcome to this deep dive into the fascinating world of Excel’s XLOOKUP function—specifically using it with RegEx (Regular Expressions). In this post, we’ll cover the basics of XLOOKUP, explore the new RegEx match mode, and demonstrate some practical examples. Whether you’re already familiar with XLOOKUP or just starting out, there’s something here for everyone!

Video

Step-by-step Tutorial

In addition to the video above, I’m also providing a full written tutorial and walking through a few exercises to demonstrate.

Exercise 1: XLOOKUP with Exact Match

Before we dive into the RegEx capabilities, let’s take a step back and look at the XLOOKUP function itself. XLOOKUP is Excel’s powerful lookup function that replaces legacy lookup functions such as VLOOKUP and HLOOKUP. Here are the required arguments of the function (there are additional optional arguments, which we’ll discuss as needed):

=XLOOKUP(lookup_value, lookup_array, return_array)

• lookup_value: The value you’re searching for.
• lookup_array: The range or array to search.
• return_array: The range from which to return a value.

To get started, let’s look at our first example.

Example:

We want to write a formula in C6 that will find the value associated with the customer code in B6 from a table named Table1.

We can write the following formula into C6:

=XLOOKUP(B6, Table1[Cust], Table1[Amt])

In this example, XLOOKUP searches the range Table1[Cust] for the value in B6 and returns the corresponding value from Table1[Amt].

In our case, it returns 841 because “MOR300” is associated with that value.

So far so good? Excellent. Now let’s kick it up a notch.

Exercise 2: XLOOKUP with Wildcards and RegEx

Now, let’s make things a bit more flexible by incorporating wildcards. Imagine we don’t know the full customer code, but we know part of it, like “30”.

One option to address this situation is to use wildcards. The asterisk can stand-in for any number of characters. So, we can include a wildcard before, after, or both, as shown below.

But, when when we write an XLOOKUP like above, we get an error:

This is because we need to somehow tell XLOOKUP that we are not looking for an exact match, instead, we are looking for a match that takes into account our wildcard characters. So, we need to specify the optional match_mode argument as 2 (wildcard match):

=XLOOKUP(B6, Table1[Cust], Table1[Amt], , 2)

In our case, it matches SOU303 and returns 601:

You can see how wildcards offer a more flexible search option.

Here’s where things get really exciting! Microsoft has recently added a new match_mode option called RegEx match. This allows us to perform even more advanced searches. RegEx can find patterns in text, which is great for complex lookups. Covering the incredible power and comprehensive pattern matching available with RegEx in this short post isn’t possible, however, there is a wealth of information on numerous websites. ChatGPT can even help create the expressions.

When using this type of match mode, we can perform a basic partial match by removing our traditional wildcard characters:

We can write the following formula in C6 which sets the match_mode argument value to 3 for regex match:

=XLOOKUP(B6, Table1[Cust], Table1[Amt], , 3)

And now we got it:

Now, this is an extremely simple example of RegEx. It is incredibly powerful, and if it seems like it may be helpful with the type of work you do, I’d encourage you to learn more. But, I do have one more exercise to illustrate how to incorporate regex into a two-column reconciliation.

Exercise 3: Reconciling Transactions with XLOOKUP and RegEx

Now let’s reconcile a check register with a bank download. In this example, we need to match transactions by both amount and vendor, but the descriptions in the two lists aren’t identical. Specifically, we’d like to write a formula in D10:D13 that will retrieve the date from the bank download where the Amount is equal to the Amount and where the Vendor name is found anywhere within the Description.

Well, in addition to using RegEx via the optional match_mode argument, we can also use the REGEXTEST function inside an XLOOKUP. The REGEXTEST function returns TRUE when the pattern sought is found.

Here’s how we handle this with XLOOKUP and RegEx:

=XLOOKUP(1, ((Table3[Amount]=C10) * (REGEXTEST(Table3[Description],B10,1))), Table3[Date])

• (Table3[Amount]=C10): This checks if the amounts match between the check register and the bank download (stored in Table3).
• (REGEXTEST(Table3[Description],B10,1)): This compares the descriptions using RegEx, allowing us to match patterns instead of exact text. It returns TRUE when the value in B10 is found in the description column.
• The 1 tells XLOOKUP to return the first instance where both conditions are true.

Note: this setup helps find the correct match even when the descriptions differ slightly, for example, “Paypal” vs “Paypal Transaction ID 12345”.

We fill the formula down, and bam:

Conclusion

As we’ve seen, the new XLOOKUP function combined with RegEx opens up a world of possibilities in Excel. From simple exact matches to advanced pattern-based lookups, this combination offers unparalleled flexibility. It allows you to:

• Perform lookups with complex patterns.
• Match data when the exact text isn’t known.
• Automate tasks like reconciling transactions, parsing text, or searching through large datasets.

If you have any other regex lookup examples you’d like me to try, please post in the comments below!

Sample File

Feel free to download the sample file.

---

FAQ

Q1: Do all versions of Excel support XLOOKUP and RegEx?
A: XLOOKUP is available in Excel 365 and Excel 2021+. The RegEx match feature is rolling out to Excel 365 users over time, and may not yet be available in your version when you read this.

Q2: Can I use XLOOKUP for vertical and horizontal lookups?
A: Yes! XLOOKUP can replace both VLOOKUP and HLOOKUP. It works for both vertical and horizontal lookups.

Q3: What’s the difference between wildcard and RegEx matching?
A: Wildcards can do basic partial match patterns (e.g., * for any characters). RegEx offers more advanced pattern matching and can identify complex text structures.

Q4: Can I combine multiple conditions in XLOOKUP with RegEx?
A: Yes, by using Boolean arrays, you can combine multiple conditions, as we did in the transaction reconciliation example.

Q5: Is RegEx case-sensitive in XLOOKUP?
A: By default, RegEx is case-sensitive. However, you can modify your patterns or use specific RegEx flags to make it case-insensitive, for example, by passing the value of 1 to the REGEXTEST function’s third argument.

Q6: Will XLOOKUP replace VLOOKUP entirely?
A: While VLOOKUP is still available, XLOOKUP offers more flexibility and is generally considered a preferred alternative.

Q7: How does XLOOKUP handle errors?
A: You can use the optional if_not_found argument to specify what should happen if no match is found.