How to use XLOOKUP with a wildcard

When you combine XLOOKUP with a wildcard, you can search for a partial match in a table, which can be incredibly useful if you have a large data set with similar but not identical values.

Here’s how you can use the XLOOKUP function with a wildcard in Excel:

Step 1: Create your table

First, you need to create the table that you will be searching in. For this example, let’s say that you have a table of products and their prices, like this:

Step 2: Use XLOOKUP to search for a value with a wildcard

Next, you need to use the XLOOKUP function to search for a value with a wildcard. In this example, let’s say that you want to search for all products that contain the letters “an” in their name. To do this, you can use the following formula:

=XLOOKUP("*an*",A2:A7,B2:B7,"",0,1)

In this formula, the “*” character is the wildcard that tells Excel to search for any text that contains the letters “an”. The “A2:A7” and “B2:B7” are the ranges of cells that contain the product names and their corresponding prices. The “” in the third argument tells Excel to return the corresponding price for the first matching product it finds. The “0” in the fourth argument tells Excel to perform an exact match, and the “1” in the fifth argument tells Excel to perform a search from the end of the range to the beginning.

Step 3: Enter the formula and view the results

Once you’ve entered the formula, press Enter to view the results. In this example, the formula will return the prices for all products that contain the letters “an” in their name:

As you can see, the XLOOKUP function with a wildcard has found all products that contain the letters “an” in their name and returned their corresponding prices.

In summary, using the XLOOKUP function with a wildcard in Excel can be a powerful tool for searching large data sets for partial matches. By following the steps outlined above, you can quickly and easily search for values that contain a specific text string in your tables.