XLOOKUP wildcard contains substring

To create a "contains substring" type lookup formula, you can use the XLOOKUP function with wildcards. In the example shown, the formula in F5 is: XLOOKUP("*"&G4&"*",data[Title],data,,2) Where G4 contains a partial string to look for and data is an Excel Table in the range B5:D16. Note that match_mode must be set to 2 to use wildcards with XLOOKUP. Note: In older versions of Excel that do not provide the XLOOKUP function, you can also use the VLOOKUP function. Both formulas are explained below.

April 14, 2025 · 1 min · 82 words · Tyler White

Table of Contents

Explanation

XLOOKUP function

TheXLOOKUP functionis a modern upgrade to the VLOOKUP function.

To start off, we providelookup_value.

Here, because we want all three columns of data, we use another small trick.

Excel formula: XLOOKUP wildcard match example

After TRANSPOSE runs, we have an array like this:

The semicolons (;) indicate avertical array.

The array lands in cell G6, and the valuesspillvertically into the range G6:G8.

you could find an example of FILTER with a “text contains” pop in matchhere.

Excel formula: XLOOKUP match text contains

XLOOKUP supports approximate and exact matching, wildcards (* ?)

Excel formula: XLOOKUP case-sensitive

Excel formula: VLOOKUP case-sensitive

Excel XLOOKUP function

Excel VLOOKUP function

Excel TRANSPOSE function