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.

# What are the Wildcard Characters?

There are three wildcard characters as follows.

Wildcard Character | Description |
---|---|

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.

Function | Description |
---|---|

AVERAGEIF | Returns the average (arithmetic mean) of all the cells in a range that meet one criterion. |

AVERAGEIFS | Returns the average of all cells in a range of cells that meet multiple criteria. |

COUNTIF | Counts the number of cells in a range of cells that meet one criterion. |

COUNTIFS | Counts the number of cells in a range of cells that meet multiple criteria. |

HLOOKUP | Looks 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. |

MATCH | Looks up values in a reference or array and returns its relative position as an integer. |

SEARCH | Finds one text string within another text string. |

SUMIF | Adds the cells in one or more rows or columns specified by multiple criteria. |

SUMIFS | Adds the cells in one or more rows or columns specified by multiple criteria. |

VLOOKUP | Looks 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.

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.

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.

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.

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.

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.

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

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

We insert the formula in cell **A1.**

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.

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.

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.

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.