How to Use Excel Wildcard Characters in Formulas

How-to-Use-Excel-Wildcard-Characters-in-Formulas-Twitter

Excel has a number of characters referred to as wildcard characters that enable you to carry out partial matching on a text string. For example, imagine you want to search or count all the people in a list whose last name is Smith. You could use wildcard characters to search for names ending in Smith irrespective of their first name.

Wildcard characters can be used in formulas, conditional formatting, filtering, and Excels search and replace feature. In this post, I will show you how to use Excel wildcard characters in formulas.

A Quick Example

Going back to the example mentioned in the introduction, imagine we have a list of students and we want to know how many of them have the last name Smith. To do this we would use the COUNTIF function as follows:

=COUNTIF(B3:B9,”*Smith”)

If we insert this formula into cell B2 it will return the value 3 as shown in the image below.

COUNTIF Function

What are the Wildcard Characters?

There are three wildcard characters as follows.

Wildcard CharacterDescription
Asterisk (*)The asterisk character can take the place of several characters either in the middle, beginning, or the end of a text string. For instance, if you search for low* this matches lower, lowered, lowering, and lowers.
Question Mark (?)The question mark character can take the place of a single character at either the middle, beginning, or the end of a text string. For instance, if you search for b?g this matches big , bit , and bug.
Tilde (~)The tilde character cancels out/nullifies the other wildcard characters. This is useful if you want to actually search for those wildcard characters. For instance, if you are searching for strings ending with a question mark such as pig?. Because the question mark is a wildcard, Excel could match pigs . To get around this, we place the tilde character in front of the question mark character as follows pig~?.

Partial matching can only be carried out on text strings. This doesn’t mean you can’t use wildcards on numbers or dates. They just need to be converted to text strings first.

Functions

There are several functions with which you can use wildcard characters with. Some common functions that can use wildcard characters are shown in the table below.

They are, perhaps, most useful when used with some form of lookup formula, search function, and functions that carry out some kind of arithmetic calculation based on the value of a range of cells similar to the COUNTIF example used at the beginning of this blog post.

FunctionDescription
AVERAGEIFReturns the average (arithmetic mean) of all the cells in a range that meet one criterion.
AVERAGEIFSReturns the average of all cells in a range of cells that meet multiple criteria.
COUNTIFCounts the number of cells in a range of cells that meet one criterion.
COUNTIFSCounts the number of cells in a range of cells that meet multiple criteria.
HLOOKUPLooks in the first row of a range of cells, finds a lookup_value then moves down the column to return the value of a cell.
MATCHLooks up values in a reference or array and returns its relative position as an integer.
SEARCHFinds one text string within another text string.
SUMIFAdds the cells in one or more rows or columns specified by multiple criteria.
SUMIFSAdds the cells in one or more rows or columns specified by multiple criteria.
VLOOKUPLooks in the first column of a range of cells, finds a lookup_value then moves across the row to return the value of a cell.

You’ve already seen how to use a wildcard with the COUNTIF function; let’s take a look at using wild cards with the VLOOKUP, SUMIF, and MATCH functions.

VLOOKUP

Okay, in this VLOOKUP example, we have a list of email addresses, all of which contain the users’ last names. Next to the email address column is another column that contains the first name of the person that owns the email address.

If you are not familiar with the VLOOKUP function, click here.

We will use the VLOOKUP function to find the first name of a person whose last name we type into cell B1. For the purposes of this example, all the email addresses contain the last name of the email owner.

We use the following formula in cell B2 to find the first name:

=VLOOKUP(“*”&B1&”*”,$A$6:$B$12,2,0)

We input the last name, lindsay into cell B1 as shown in the image below.

VLOOKUP Wildcard

Notice that the lookup_value “*”&B1&”*” is where the wildcard characters are used. In this example, the wildcard character asterisk (*) is added to either side of the string that is input by the worksheet user to create the lookup_value.

When we use this combination, there will be a match on any text string so long as it contains the value in cell B1. In our example, this means we could have any characters at the beginning, end or both so long as the text string we are searching for contains the last name lindsay.

Notice that when we create the lookup_value, we use the ampersand (&) operator and quotation marks (). This is because the lookup_value has to be a text string.

When we use text strings in a formula, we have to wrap the text in quotation marks (). We use the ampersand (&) operator to join (concatenate) text strings together.

Breaking this formula down:

  • lookup_value – the lookup_value in our example ends up being *lindsay*.
  • table_array – the range of cells that the VLOOKUP function uses. The VLOOKUP function searches the first column of the table_array for the lookup_value *lindsay*
  • col_index_num – this is the column in the table_array that contains the value we want to return. In this example, column 2 contains the first names.
  • range_lookup – the optional parameter that determines the type of matching. We use the value 0 (false) because we want an exact match. In this example, the lookup must contain the word “lindsay,” not the closest approximation.

To find the email address, we could use a very similar formula to the one used to find the first name. For example, we could use the formula:

=VLOOKUP(“*”&B2&”*”,$A$6:$B$12,1,0)

We would simply type this formula into cell B3 to get the email address as shown below.

VLOOKUP Wildcards Formula 2

This kind of search might be useful when searching for customer contact details.

The method used in this example is almost identical when using the function HLOOKUP.

SUMIF

For our SUMIF example, we have a worksheet that has a list of computing device model numbers. Each model number starts with the type of device it is; for example Desktop, Laptop and Tablet. Next to each model number is a second column that contains the sales figures for each model as shown in the image below.

SUMIF Example Data

We are going to use the SUMIF function to find the total value of sales for each device type, Desktop, Laptop and Tablet. If you are not familiar with the SUMIF function, click here.

The first thing we are going to do is format some  cells to display the total sales values of each device type. So create two rows of cells: D1:F1 to hold the cell titles and D2:F2, where we will insert our formulas containing the SUMIF functions.

SUMIF Formula and Title Cells

The formulas that are going to be in the cells D2:F2 will be very similar to the one below that will be used in cell D2.

=SUMIF($A$2:$A$46,”=Laptop*”,$B$2:$B$46)

When we insert the formula in the spreadsheet, it returns the value $98,100.00 as shown below.

SUMIF Formula Wildcards

 

Breaking this formula down, this is what’s happening:

  • range – this is the range of cells that we will search to match the text string in the criteria
  • criteria – this is the value we are searching for and in which we use the asterisk (*) wildcard character. Notice that this time, we only use the asterisk (*) wildcard character at the end of the text string Laptop*. This means we only want model numbers that start with the word Laptop.
  • sum_range – these are the values that will be summed if we get a match.

The method used above for the SUMIF function can be applied to the COUNTIF and AVERAGEIF functions. When using SUMIFS, COUNTIFS, and AVERAGEIFS functions, you have additional criteria against which you can match, but the wildcard method is the same.

MATCH

In this example, we look at the MATCH function. The MATCH function doesn’t return a value from a range of cells; it returns the relative row or column number of a lookup value in a range of cells.

In this example, we use the MATCH function to find the row position of a name in a column of cells. Why is this useful? Well, the MATCH function can be used in combination with the INDEX function to create a very flexible lookup formula that gets around many of the problems of the VLOOKUP function. To get an in-depth explanation of how to use the INDEX and MATCH functions in combination, read my blog post by clicking here.

Using the dataset in the image below, we’ll search the range of cells A2:A8 to find the person who has a first name that starts with the letter A, followed by three characters and has the last name Smith.

MATCH Function Dataset

To do this, we are going to use the following formula:

=MATCH(“A??? Smith”,A2:A8,0)

We insert the formula in cell A1.

MATCH Formula

The formula returns the value 3, which represents the first row (cell A2) in the range of cells A2:A8. Let’s break the formula down:

  • lookup_value – is “A??? Smith” this means the match function will match a text string that starts with the letter A, and any 3 characters followed by a space and then the name Smith. Remember that the question mark (?) character replaces only a single character.
  • lookup_array – is the range of cells A2:A8
  • match_type – is 0, which means the MATCH function carries out an exact match.

Using the Tilde (~) Character

In this final example, we’ll look at how we would use the tilde (~) wildcard character. We will search a list of animals that have both the singular and plural noun. Each of the singular terms is followed by a question mark as shown in the image below.

MATCH Function with Tilde Dataset

In this example, we use the MATCH function to return the row number of the text string Dog?.

If we search using the following function:

=MATCH(“Dog?”,A2:A8,0)

It returns the value 1 as shown in the image below.

MATCH Formula without Tilda

The formula returns the value 1 because it assumes that the question mark (?) character is being used as a wild card and, therefore, matches the first text string that starts with “Dog” and then any other single character. In this instance, the text string “Dogs” is a match.

If we want to match a text string that contains the question mark (?) character, then we need to use the tilde (~) character first. To do this we simply modify the formula as follows.

=MATCH(“Dog~?”,A2:A8,0)

Notice that all we have done is place the tilde (~) character in front of the question mark (?) character. This tells Excel not to treat the question mark (?) character as a wildcard character but to use the text string “Dogs?” as the lookup_value. We insert the formula into cell A1 as shown below.

MATCH Formula Using Tilda

When this is done, the formula returns the value 2, which is correct for “Dog?“.

Wrap-up

That’s it for this blog post. Hopefully, you found it useful. The wildcard characters can be extremely useful when searching large datasets, and you’re not certain of the exact value you want to use, or when you want to search for values that might be used as part of a larger text string such as part or model numbers.

If you liked this post, please share it with your friends on Facebook or Twitter.

For other tools and resources for learning more about Excel, check out my resources page.

To get a copy of the example spreadsheet used in this blog post and receive a copy of our newsletter, sign up to our email list below.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit