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 here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use XLOOKUP to Return All Matches
Excel: How to Use XLOOKUP with Multiple Criteria
Excel: How to Use VLOOKUP with COUNTIF
