XLOOKUP and FILTER with Wildcard

The objective of this tutorial is to show how to use wildcards with the XLOOKUP and FILTER functions in Excel. We will learn how to perform partial matches and search for specific patterns within a dataset.

Video

Walkthrough

Let’s take it one exercise at a time, starting with our XLOOKUP warm-up.

Exercise 1: XLOOKUP warm-up

Let’s start by understanding how the XLOOKUP function works without using any wildcards.

Considering the following worksheet:

We would like to write a formula in C10 that returns the Price for the Item found in B10. There are several options here, but in this example, we’ll use the XLOOKUP function. So, we write the following formula:

=XLOOKUP(B10,B15:B24,C15:C24)

• B10 is the value we are trying to find (Item)
• B15:B24 is the range we are searching (Item)
• C15:C24 is the range that has the value we want to return (Price)

We hit Enter and bam:

We can see that XLOOKUP returned the price 453, which is exactly right.

Now that we are warmed up, let’s take the next step and use a wildcard with XLOOKUP.

Exercise 2: Partial match with wildcard

Now, let’s see how we can perform a partial match using a wildcard.

If we update the value in B11 to XY, since there is not an exact match XLOOKUP returns an error:

If we wanted the search value, XY, to match to XY200, we can use the asterisk wildcard. But, when we update the search value in B11 to XY*, we still get an error:

So, what are we supposed to do?

Well, all we need to do is tell the XLOOKUP function that we want to enable wildcard support. We do this by setting the match_mode argument to 2. So we update our formula as follows:

=XLOOKUP(B11,B16:B25,C16:C25,,2)

Now we hit enter, and yes … we got it:

But … the question at this point is: what happens if there are multiple rows that match?

Well, XLOOKUP is a lookup function, and consistent with other lookup functions, it stops at the first match. That is, it returns a result for the first match and doesn’t return multiple matching rows.

So, what are we supposed to do?

Well, that brings us to the next exercise.

Exercise 3: Return multiple results with FILTER

In some cases, you might need to return all the matching results instead of just the first one. We can achieve this using the FILTER function.

The basic format for the FILTER function is:

=FILTER(array, include)

• array is the range that has the value we want to filter and return
• include is an expression that defines the filter

Although the FILTER function doesn’t have an argument to enable wildcard support like XLOOKUP, we can use helper functions.

If we just wanted to match the first 2 characters, we can leverage the LEFT function like this:

=FILTER(B19:C28,LEFT(B19:B28,2)=C8)

Or, if we wanted to match the text anywhere within the cell, we can leverage the SEARCH function like this:

=FILTER(B19:C28,NOT(ISERROR(SEARCH(C8,B19:B28))))

In this illustration, either formula will return all rows that match XY as shown below:

Yay … we did it!

Conclusion

By using these techniques, we can perform partial matches and search for patterns within a dataset using wildcards. If you have any suggestions, improvements, questions, or alternatives, please post a comment below … thanks!

Sample file

FAQs (Frequently Asked Questions)

Q: Can I use a different wildcard character instead of an asterisk?

A: Yes. The asterisk (*) stands in for any number of characters. The question mark (?) stands in for a single character.

Q: Are wildcards case-sensitive?

A: No, wildcards are not case-sensitive. Excel will treat upper and lower case letters as equivalent.

Q: Can I combine multiple wildcards in a single search?

A: Yes, you can combine multiple wildcards with XLOOKUP to create more complex search patterns.

Q: How can I use wildcards with numerical values?

A: Yes, wildcards can stand in to represents text and numbers.

Q: Can I use wildcards with other Excel functions?

A: Some other functions, including VLOOKUP and SUMIFS, do operate with wildcards as well.