XLOOKUP with Regex Match: How It Works

XLOOKUP does not natively support regex, but you can combine it with functions like REGEXTEST (in Excel 365) or use helper columns with FIND/SEARCH to achieve pattern-based lookups. This article explains the practical methods, their limitations, and when to use each approach.

Background: Why Regex and XLOOKUP Seem Incompatible

XLOOKUP is designed to match exact values, approximate values, or wildcard characters (* and ?). It does not accept regular expressions (regex) as a matching mode. Regex is a powerful pattern-matching language used in many programming contexts, but Excel’s built-in lookup functions historically relied on simpler wildcard syntax. In 2022, Microsoft introduced REGEXTEST, REGEXEXTRACT, and REGEXREPLACE for Excel 365, but these are separate functions—they do not change XLOOKUP’s matching behavior.

This gap often surprises users who come from programming backgrounds or who need to match patterns like phone numbers, email addresses, or product codes with variable formats. The solution is to use regex functions to create a boolean array that XLOOKUP can use as its lookup array.

How to Use REGEXTEST with XLOOKUP (Excel 365)

If you have Excel 365 (current channel), you can use REGEXTEST to test each cell in a range against a pattern, then feed that array of TRUE/FALSE values into XLOOKUP as the lookup array. The formula structure is:

=XLOOKUP(TRUE, REGEXTEST(range, pattern), return_range)

Here, range is the column you want to search, pattern is the regex pattern (as text), and return_range is the column from which to return the matching value. Because REGEXTEST returns an array of the same size as the range, XLOOKUP can find the first TRUE and return the corresponding value.

Example: To find the first product code that starts with “AB” followed by three digits, use:

=XLOOKUP(TRUE, REGEXTEST(A2:A100, "^AB\d{3}"), B2:B100)

This will return the value from column B for the first row in A2:A100 that matches the pattern.

Alternative: Using Helper Columns with FIND or SEARCH

If you don’t have Excel 365, or you prefer a more transparent approach, you can create a helper column that uses FIND or SEARCH to test for a pattern. For simple patterns, you can use wildcards with COUNTIF or SUMPRODUCT, but for true regex-like flexibility, you might need a UDF (User Defined Function) in VBA. However, a simpler method is to use a combination of ISNUMBER and SEARCH for substring matches, or use wildcards with XLOOKUP’s match_mode set to 2 (wildcard).

For example, to find a value that contains “AB” anywhere, you can use:

=XLOOKUP("*AB*", A2:A100, B2:B100, , 2)

This uses wildcard matching, which is not regex but works for many common substring needs. For more complex patterns, a helper column with a formula like =IF(ISNUMBER(SEARCH("AB",A2)),1,0) can be used, then XLOOKUP for 1 in that helper column.

Choosing Between REGEXTEST and Helper Columns

Which method you choose depends on your Excel version and the complexity of the pattern:

• REGEXTEST is ideal if you have Excel 365 and need complex regex patterns. It’s dynamic and doesn’t require helper columns.
• Helper columns work in any version and are easier to debug, but they add clutter and require manual updates if the range changes.
• Wildcard XLOOKUP is the simplest for substring or prefix/suffix matches, but it only supports *, ?, and ~ as escape characters—not full regex.

For example, if you need to match an email pattern like [a-z]+@[a-z]+\.com, REGEXTEST is the only practical built-in option. For a simple prefix like “AB-“, wildcards work fine.

Common Problems and Pitfalls

When using REGEXTEST with XLOOKUP, several issues can arise:

• Array size mismatch: Ensure the range in REGEXTEST and the return range have the same number of rows. If not, XLOOKUP returns an error.
• Regex syntax errors: Excel’s regex engine (based on ICU) uses a specific syntax. Backslashes must be escaped in the formula (e.g., \d for digits).
• Case sensitivity: By default, REGEXTEST is case-insensitive. To make it case-sensitive, use the third argument: REGEXTEST(range, pattern, 1).
• Spill behavior: REGEXTEST returns a dynamic array that spills. If there is data in adjacent cells, it may cause a #SPILL! error. Ensure the area below the formula is empty.
• Performance: Using REGEXTEST on a large range (thousands of rows) can slow down calculations. Consider limiting the range to the actual data area.

Comparison: XLOOKUP with Regex vs. Other Methods

Other lookup functions can also be combined with regex:

• INDEX/MATCH: Use =INDEX(return_range, MATCH(1, REGEXTEST(range, pattern)*1, 0)). This is an array formula (enter with Ctrl+Shift+Enter in older versions) and works similarly.
• FILTER: =FILTER(return_range, REGEXTEST(range, pattern)) returns all matches, not just the first. This is useful when you need multiple results.
• VLOOKUP: VLOOKUP cannot directly use regex; you would need a helper column as well.

XLOOKUP’s advantage is its simplicity—no array entry required—and its ability to return from any column to the left or right.

Performance and Limitations

Regex is computationally heavier than wildcard matching. On a dataset of 100,000 rows, REGEXTEST may cause noticeable lag. Also, XLOOKUP with REGEXTEST returns only the first match. If you need all matches, use FILTER instead.

Excel’s regex engine supports a wide range of patterns, including character classes, quantifiers, groups, and lookaheads. However, it does not support backreferences or possessive quantifiers. For most practical lookup needs, the supported features are sufficient.

Frequently Asked Questions

Can I use XLOOKUP with regex in Excel 2019 or earlier?

No. XLOOKUP itself is only available in Excel 365 and Excel 2021. REGEXTEST is only in Excel 365. In older versions, you must use helper columns with SEARCH or VBA.

Does XLOOKUP support wildcards?

Yes, XLOOKUP supports wildcards when you set match_mode to 2 (wildcard). The wildcards are * (any sequence) and ? (single character). This is not regex but covers many common patterns.

How do I make REGEXTEST case-sensitive?

Use the third argument of REGEXTEST: REGEXTEST(range, pattern, 1). The default is 0 (case-insensitive).

Why does my formula return #N/A?

#N/A means no match was found. Check that the pattern is correct and that the range in REGEXTEST is the same size as the return range. Also, ensure the pattern is written as a string with quotes, and escape backslashes properly.

Can I return multiple matches with XLOOKUP and regex?

No, XLOOKUP returns only the first match. Use FILTER with REGEXTEST to return all matching values.

Is there a way to use regex with VLOOKUP?

Not directly. You would need to create a helper column that tests the pattern and returns a flag, then use VLOOKUP on that flag.

Related

• Get Last Name From Name With Comma
• Filter on First or Last n Values?
• Charging Mobile Devices: Best Practices & Facts