Hi - Dave here.
Happy Friday!
Last week, I shared an
article introducing Regular Expressions (regex) in Excel. This is a big upgrade in Excel's text-handling capabilities!
I also mentioned that two functions, XLOOKUP and XMATCH, now also support regex. What does that mean, exactly, and how might you use it? Great question!
The main benefit of regex with XLOOKUP (and XMATCH) is the ability to construct very specific matching patterns for your lookups. For example, in the worksheet below, we have some product codes in column B. We want to look up the price in column C using only the numeric part of the code, ignoring the characters before and after.
A classic solution to this problem is to use wildcards in the lookup value, as seen in the workbook below. However, that won't work because the number 56 also appears inside other codes. Tricky!
Regex can get us out of this problem by making it possible to create a very specific pattern for the numbers we want to match, like this:
=XLOOKUP("[A-Z]{3}56[A-Z]{2,3}",B5:B16,C5:C16,,3)
Roughly translated, we ask XLOOKUP to match "3 uppercase letters A-Z, followed by 56, followed by 2-3 uppercase letters A-Z". This is a much more specific pattern, and it works!
You can see that regex is very powerful. Click the link below to read a detailed explanation and to download the worksheet. The worksheet includes the wildcard formula that fails plus the regex formula that does work.
[
Download the workbook and read the full explanation]
Note: Regex support was added to XLOOKUP in December 2024, so this feature is only available in Excel 365 for now.
Google search tip
Like many websites, we get a lot of traffic from people searching Google. These days, you might notice the thing you want is buried below AI generated content and paid product promotions, as this
great comic perfectly illustrates.
If you want to find something on
Exceljet.net, I humbly recommend adding “exceljet” to your Google search. For example, instead of searching "excel xlookup", you can search for "exceljet xlookup" or "excel xlookup exceljet". We are 100% human and committed to publishing high-quality resources that help you work faster in Excel.
Last email of 2024
This will be the last email newsletter I send in 2024. I hope you find the topics interesting and useful. Excel is changing faster than I’ve ever seen. The formula engine has added almost 50 new functions and now supports regex too. We are working hard to keep up with the rapid pace and, of course, keep you up-to-date with modern solutions to your Excel challenges.
All the best to you and yours, and I'll talk to you again in 2025!
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend and Happy New Year!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I mostly talk about useful Excel formulas. You can sign up on our home page.