This problem is trickier than it looks.
Each product code begins with 3 uppercase letters and ends with 2 or 3 uppercase letters.
In the middle of the product code is a number between 2 and 4 digits.
This is the number we want to use for a lookup value.
All formulas below refer to the worksheet shown above.
Download the workbook above and follow along.
Simple XLOOKUP formula
By default, XLOOKUP will perform an exact match.
To actually use a wildcard match, we need to provide some wildcards.
This seems like it should work.
However, the formula returns 78, which is not correct.
Well, if “56” only appeared once in the product codes, itwouldwork.
However, “56” appears inside three product codes: KP563MN, QR56DE, and HJ3456TU.
What’s happening here is that XLOOKUP is matching the first product code that contains 56, KP563MN.
Standard XLOOKUP behavior when there are multiple matches.
Can we make XLOOKUP find the right code?
like this:
The “?”
wildcard means “one character of any kind”.
In fact, the formula abovedoes return the correct pricefor PQR56DE, which is $46.00.
It’s time to roll out the big guns: Regular Expressions, called “regex” for short.
Regex, short for Regular Expressions, is a powerful tool for pattern matching in text data.
Regular Expressions have been around for decades, but only recently arrived in Excel.
If you are new to Regular Expressions,see this overview.
This is the price for KPX563MN, the first product code that contains “56”.
In other words, just by enabling regex, we get a working “contains” throw in match.
We’ll get the same result if we use thenumber56 as the lookup value.
The first step is to add a pattern to match thebeginningof the code.
As noted above, the product codes always start with 3 uppercase characters.
Unfortunately, this formula also returns an incorrect result because KPX563MN is the first code to pass this test.
This is where the power of regex patterns starts to really shine.
With this adjustment, the formula finally returns the correct result of 46.
The final step is to adjust the formula to get the lookup number from cell F4.
When a user types a new valid number in cell F4, the formula will return a new result.
Matching the entire cell
By default, regex will match anysubstringsthat match the pattern.
The only change to this formula is the^at the start and the$at the end of the pattern.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
for partial matches, and lookups in vertical or horizontal ranges….
