Microsoft Excel Tutorial: Using a RegEx match in Excel XLOOKUP
To download the workbook from today: https://www.mrexcel.com/youtube/NZybVpM_dI4/
Welcome to another exciting update from MrExcel! In today’s video, we’re diving into a major breakthrough: RegEx support in XLOOKUP and XMATCH, which was just rolled out on August 14th, 2024. This new feature has been made available to 50% of insiders and promises to revolutionize how you work with data in Excel. If you enjoy the content, don’t forget to hit that Like button below to help more people discover this video!
We’ll start by exploring the new RegEx functionality in XLOOKUP and XMATCH. Imagine you have a dataset involving product deliveries to various cities with complex zip code ranges. Previously, this would have required convoluted formulas and multiple steps. Now, with RegEx, you can streamline your searches. I’ll walk you through a practical example where we use RegEx to match zip codes and return the correct order IDs—all in one fell swoop!
In the demo, I show you how to use RegEx patterns to identify zip codes for Los Angeles and San Diego within a larger dataset. By leveraging a RegEx pattern created with the help of Chat-GPT, we simplify the lookup process in XLOOKUP. You’ll see how changing the match mode argument to ‘3’ enables RegEx matching, allowing you to pull up data quickly and accurately. This new feature not only saves time but also ensures precision in your data analysis.
We’ll also test the case sensitivity of the RegEx and its effectiveness with zip-plus-four formats. Whether you’re searching for raspberries or kiwis, this update demonstrates how powerful and versatile the new RegEx support can be. The video includes practical examples and a step-by-step guide to implementing these changes in your own spreadsheets.
Finally, I’m curious to hear your thoughts on what other Excel functions could benefit from RegEx support. Drop your suggestions in the comments below—I’ll be forwarding them to the Excel team! If you enjoyed this tutorial, please Like, Subscribe, and Ring the Bell to stay updated with the latest from MrExcel. Thanks for watching, and see you next time for more Excel tips and tricks!
Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
Table of Contents
(0:00) Excel adds Regex support to XLOOKUP and XMATCH
(0:22) Figure out L.A. or San Diego from Zip Code
(0:57) You could do an extra XLOOKUP
(1:08) RegEx code for zip code ranges
(1:37) Putting RegEx as first argument in XLOOKUP
(2:05) Specifying Regex as the Match Mode in XLOOKUP
(2:54) Wrap up
(3:13) Please Like, Subscribe, Ring the Bell
#excelnew #excelformula #excelformulasandfunctions #xlookup #regex
This video answers these common search terms:
RegEx in XLOOKUP and XMATCH in Excel
XLOOKUP RegEx support tutorial
How to use RegEx in Excel XLOOKUP
XMATCH RegEx functionality explained
Excel RegEx match mode August 2024
Advanced Excel lookup techniques
XLOOKUP with regular expressions example
Using RegEx for zip code matching in Excel
New Excel features RegEx support
Excel XLOOKUP RegEx tips and tricks