If you are reading this, I guess you already know how the VLOOKUP function works. This function is so sensitive that it can detect even trailing and leading spaces in a cell – something the human eye can barely see.
What if you are not so sure of the exact value you are searching for? Does that mean VLOOKUP cannot find a partial text match? Far from that.
With VLOOKUP wildcard, your searching capabilities can exceed that of Google. With this tool, you can use VLOOKUP to search for any part of a cell’s content.
For instance, you can search for any value that begins or ends with a particular letter or string. The same way you can search for any cell that contains a particular word string. Nothing could be more flexible.
Consider the following table for example. Assuming you want to look up an employee name (Kevin Ashton) to retrieve his salary. Using the asterisk wildcard, you can search by his first name only (Kevin*), or by his last name (*Ashton). You can also search for any name beginning with K (K*) or ending with N (n*).
You can also use the question mark (?) wildcard to replace a single character in a search. For example, a search for Ke?in Ashton will find Kevin Ashton. Without these wildcard characters, VLOOKUP cannot perform a partial match search by default.
Before I begin illustrating with examples, let’s get the concepts straight.
To follow along, you can download the example file below:
What is a wildcard in Excel?
Wildcard is a symbol use for advanced searching. There are three different types of wildcards. They stand in for any character or characters in a search term.
For example, a search for re*ter will find any word that begins with the letters re and ends with the letters ter. If the data you are searching through has a word like reporter as in the data above, it will be spotted. This simply means that the middle letters por that was missing in the search term is being replaced by the asterisk (*). The asterisk (*) between the letters is called a wildcard symbol.
Excel Wildcard symbols
Listed below are the three wildcard symbols in excel.
- The asterisk wildcard (*): this wildcard matches any sequence of characters. It stands in for a group of one or more characters after, before or in between text. This means that with this wildcard, you can search for words that begin with a particular letter or letters, you can search for words that end with a particular letter or letters, you can search for words that contain a particular letter or letters in between them. You can also use the asterisk wildcard as many times as possible in a single search. For instance, a search for re*t*r is valid. It stands in for two sets of missing characters. It will search for a word that begins with the letters re, have the letter t within the word, and ends with the letter r. This search can find words like reporter, repudiator or requester because they all match the conditions of the search term.
- The question mark (?) wildcard: The question mark as a wildcard, is used to stand for any single character. For instance, if you search for f?st, you can find fast or fist but not foist or first. Unlike the asterisk wildcard, this wildcard can only represent a single character.
- The tilde (~) wildcard: This particular wildcard is used to search for the above two special characters. When you want to search for one of these special characters, the asterisk or the question mark, you will need to use the tilde (~) before it to handicap its functionality as a wildcard. For instance, the search for value~? or value~* searches for cell values that contain the question mark or the asterisk symbols respectively. Things will get clearer when you see some examples.
VLOOKUP wildcard Examples
I am convinced that you now understand how the various wildcard characters work in Excel. Let me complete this guide with some practical VLOOKUP examples where the wildcard characters are used within the formula.
Example 1 – Search for beginning words or characters
In this example, you’ll learn how to use the asterisk wildcard character to perform a partial match search in the VLOOKUP function.
Taking the following data as an example, let’s say you want to search for an employee by his or her first name to return his or her salary using the VLOOKUP function.
Looking at the worksheet above, the formula made an attempt to find an employee by the first name Chase in order to retrieve his salary. However, the formula returned an error. This error is because there is no employee by the name Chase. Even though there is Chase Riley in the list, it is not the same as your search term.
Excel doesn’t know your intention until you make it known. So, to search for employees using their first names only, you need to specify that in your formula. This is where the VLOOKUP wildcard comes in.
To search for a name beginning with Chase, you don’t just write Chase in the formula like in the example above. What you should do is append the name Chase with the asterisk (*) wildcard symbol. This means that the search term should rather be Chase* instead of Chase.
Here’s how the formula for this type of search should look like:
Therefore, to search for a cell whose content begins with a particular word or letters, simply append the word or letters with the asterisk (*) symbol.
It is also possible to search for names or words that begin with a particular character or characters. For instance, if you want to look up a name that begins with C, just append the letter C with the asterisk symbol – C*.
The formula should then look like this:
Example 2 – Search for ending words or characters
Let’s say you want to search for an employee using only the last name to return his or her salary. This is almost the same task as in the previous example. The solution too is almost the same as well.
To lookup a name ending with say Riley, you shouldn’t just write Riley into the formula. Instead, you should prefix the last name Riley with the VLOOKUP wildcard (*). The lookup value in such a formula should rather be *Riley instead of Riley.
Here’s how the formula for this type of search should look like:
The idea in this example is the same as in the previous one. To search for a cell whose content ends with a particular word or letters, prefix the word or letters with the asterisk (*) wildcard symbol.
Example 3 – Search for a cell containing specific letters
The VLOOKUP wildcard can also help you search for cells that contain a particular letter or letters in-between the content. Consider the first column in the example below: