How can I utilize the XLOOKUP function with a wildcard in Excel?

By /

The XLOOKUP function in Excel allows users to easily search for specific values in a range of data and retrieve corresponding information. By incorporating a wildcard symbol, such as an asterisk (*), users can further enhance the functionality of the XLOOKUP function by performing partial or fuzzy searches. This allows for more flexibility in finding matching values and can save time and effort in data analysis tasks. By utilizing the XLOOKUP function with a wildcard, users can efficiently and accurately retrieve desired information from large sets of data in Excel.

Excel: Use XLOOKUP with Wildcard

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:

Excel XLOOKUP with wildcard

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 .

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

x

Related terms:

  1. How to use XLOOKUP with Wildcard in Excel?
  2. How to use the wildcard in SUMIFS function in Excel?
  3. How can I utilize the geom_errorbar() function in ggplot2 to create error bars in my plots?
  4. How can I utilize the colMax function in R for finding the maximum value in a specific column?
  5. How can I use wildcard characters with the FILTER function in Google Sheets?
  6. How to use XLOOKUP to Sum All Matches in Excel?
  7. How to use wildcard characters in Google Sheets Query
  8. How can MySQL utilize a Case Statement with Multiple Conditions?
  9. How can I use XLOOKUP from another sheet in Google Sheets?
  10. How to Use XLOOKUP in Google Sheets (With Example)