As promised, Excel 365’s Regular Expression support has been extended to include both Xlookup() and XMatch() functions. Regex adds a huge amount of power to matching results from a table.
Back in July, Excel 365 added three Regular Expression (regex) functions, RegexTEST(), RegexEXTRACT() and RegexREPLACE(). At the time Microsoft promised the obvious extension of Regex.
It’s now arrived now Xlookup() and XMatch() functions now have an extra ‘Match Mode’ parameter.
Choose “Regex match” so the first parameter “lookup value” can do a lot of cool stuff.
Have options for lookup
A simple but powerful possibility is giving multiple choices to lookup. That lets you match both a full name, short version or abbreviation to find.
The | pipe in regex means logical OR. Lookup “USA|United States” will match either “USA” or “United States”
Try one of these
“CEO|Chief Executive Officer|Da Boss”“London|LON|LDN”“UK|United Kingdom|Great Britain|GB”“USA|US|United States”“AU|Australia|Oz”Or for cities with more than one airport try something like
“JFK|LGA|EWR”Or
“LHR|LCY|LGW|STN|LTN|SEN”Regex is capable of a lot more, see Starting off with Regular Expressions in Excel
Who gets it?
Excel 365 for both Windows and Mac, starting with Beta Insiders releases
- Windows: v2408 build 17931.20000
- Mac: v16.89 build 24080715
