Table of Contents
XLOOKUP is a powerful function in Excel that can be used to perform lookups with wildcards. This is especially useful when you need to match a pattern of data that may have a variable or unknown value. To use XLOOKUP with wildcards, you need to use the asterisk (*) character as a wildcard and then specify the criteria for the lookup. XLOOKUP will then search for any value that matches that criteria. This is a great way to quickly find data that you may not know the exact value of.
You can use the XLOOKUP function in Excel to look up some value in a column and return the corresponding value in a different column.
To use wildcard characters when looking up a value, you can use the following syntax:
=XLOOKUP("*"&E1&"*", A2:A11, B2:B11,,2)
This particular formula finds the first value in the range A2:A11 that contains the partial text in cell E1 and returns the corresponding value in the range B2:B11.
The following example shows how to use this formula in practice.
Example: How to Use XLOOKUP with Wildcard in Excel
Suppose we have the following dataset in Excel that contains information about points scored by various basketball players:
Suppose we would like to look up the value in the Team column with the partial text “ock” and return the corresponding value from the Points column.
Suppose we attempt to use the following formula to do so:
=XLOOKUP(E1, A2:A11, B2:B11)
The following screenshot shows how to use this formula in practice:
The formula returns #N/A since there is no value in the Team column that is equal to “ock” as an exact match.
However, we can use the following syntax with wildcards to look up the value in the Team column that contains “ock” as a partial match:
=XLOOKUP("*"&E1&"*", A2:A11, B2:B11,,2)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 14, which represents the value in the Points column for the first row that contains “ock” in the Team column.
Note: You can find the complete documentation for the XLOOKUP function in Excel .