XLOOKUP with Match Mode 3 in Excel (Regex Match)

XLOOKUP becomes more powerful in Excel with the new match mode 3, which enables using regex patterns as the search key.

This is helpful in various scenarios such as case-sensitive matching, partial word matching, specifying multiple search keys to match either of the first occurrences, and more.

In this tutorial, you will find examples of common lookup scenarios that you can achieve using match mode 3 in Excel.

Introduction

Regular expressions in XLOOKUP provide a way to describe sets of character strings for lookup. To use this, you must specify match mode 3, which is the 5th argument in the function. XLOOKUP checks whether a particular string matches the set described by a regular expression in the lookup range and returns the corresponding value from the result range.

Here are some common regex patterns you can use when specifying match mode 3:

. – Any character
^ – Beginning of text
$ – End of text
(?i) – Case-insensitive match
[a-z] – Lowercase letters a to z
[A-Z] – Uppercase letters A to Z
[0-9] – Any numerical digit
\b – Word boundary
x* – Zero or more occurrences of ‘x’
x+ – One or more occurrences of ‘x’
x? – Zero or one occurrence of ‘x’
x{n,} – ‘n’ or more occurrences of ‘x’
[\d] – Digits
[^\d] – Non-digits
[0-9A-Za-z] – Alphanumeric characters
[A-Za-z] – Alphabetic characters
x|y – ‘x’ or ‘y’

Excel adheres to the PCRE2 (Perl Compatible Regular Expressions) flavor of regex, giving access to a rich set of regex features, including complex pattern matching, capturing groups, lookarounds, and more. This provides advanced text search functionality in Excel.

To enable regex-based lookup in XLOOKUP, use match mode 3. Here’s the syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Examples of XLOOKUP with Match Mode 3 in Excel

Below are some practical examples of using regex patterns in XLOOKUP with match mode 3.

1. Basic Use Case

=XLOOKUP("apple", A:A, B:B, "Not found!", 3)

This matches the keyword “apple” in column A and returns the corresponding value from column B. The match is case-sensitive and can be exact or partial. If no match is found, the formula returns “Not found!”.

2. Starts With

=XLOOKUP("^Gravel", A:A, B:B, "Not found!", 3)

Matches strings that start with “Gravel” in column A and returns the first matching value from column B. Useful for entries like “Gravel 5-10 mm”, “Gravel 10-20 mm”.

Wildcard alternative (case-insensitive):

=XLOOKUP("Gravel*", A:A, B:B, "Not found!", 2)

Key Difference: Match mode 3 is case-sensitive, while match mode 2 (wildcards) is not.

3. Ends With

=XLOOKUP("10-20 mm$", A:A, B:B, "Not found!", 3)

Matches items ending with “10-20 mm” in column A.

Wildcard alternative:

=XLOOKUP("*10-20 mm", A:A, B:B, "Not found!", 2)

4. Case-Sensitivity

=XLOOKUP("(?i)^Gravel", A:A, B:B, "Not found!", 3)
=XLOOKUP("(?i)10-20 mm$", A:A, B:B, "Not found!", 3)

Adding the (?i) flag makes the regex case-insensitive. If you want to enforce case sensitivity again later in the pattern, you can use (?-i) or simply remove the inline flag.

5. Alphanumeric Match

=XLOOKUP(".*[a-zA-Z].*[0-9].*|.*[0-9].*[a-zA-Z].*", A:A, B:B, "Not found!", 3)

Matches strings containing both letters and numbers, regardless of order.

XLOOKUP with Match Mode 3 for Alphanumeric Search Key Lookup in Excel

6. Multiple Match (This or That)

=XLOOKUP("UK|United Kingdom", A:A, B:B, "Not found!", 3)

Matches either “UK” or “United Kingdom”.

7. Match Multiple Occurrences of a Keyword

=XLOOKUP("(?:.*apple){2}", A:A, B:B, "Not found!", 3)

Finds strings containing “apple” at least twice.

8. Lookup Inside Brackets

=XLOOKUP("\[(.+)\]", A:A, B:B, "Not found!", 3)

Finds values enclosed in square brackets [ ].

9. Word Boundary Lookup

=XLOOKUP("\b(apple)\b", A:A, B:B, "Not found!", 3)

Matches “apple” as a whole word, preventing unintended matches like “pineapple”.

10. Exact Match with Case Sensitivity

For an exact match, we can use a regular formula, but this is case-insensitive:

=XLOOKUP("apple", A:A, B:B, "Not found!", 0)

For case-insensitive exact matches using regex:

=XLOOKUP("(?i)^apple$", A:A, B:B, "Not found!", 3)

To enforce case sensitivity, simply remove (?i). The XLOOKUP function with match mode 3 (regex) is case-sensitive by default. The (?-i) flag is only needed if case insensitivity was enabled earlier in a more complex regex pattern.