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 the 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. In 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 here.
What is a wildcard in Excel?
The wildcard is a symbol used 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 a 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 are 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 on 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:
=VLOOKUP(“Chase*”,Employee_Info,5,FALSE)
See screenshot:
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:
=VLOOKUP(“C*”,Employee_Info,5,FALSE)
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 look up 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:
=VLOOKUP(“*Riley”,Employee_Info,5,FALSE)
See screenshot:
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:
You notice that the product code column contains codes that all have the same prefix and suffix. However, the numerical values between the codes differ for each product.
Our goal here is to find a product by using only the numerical values between the codes and retrieve the unit price for that product. So, let’s assume you want to look up a product with a product code that has 005 between the code.
In a case like this, you should surround the lookup value (005) with the asterisk (*) wildcard symbol. The search term should, therefore, be *005* and not 005.
Here’s how the formula for this type of search should look like:
=VLOOKUP(“*005*”,Employee_Info,4,FALSE)
See screenshot:
Therefore, to search for a cell containing a particular set of characters within the cell’s content, simply surround the characters with the asterisk (*) wildcard symbol.
To make this interesting, you can use a cell reference to get only the numerical values and concatenate the wildcard symbol inside the function. This way, you’ll only have to type the product code in the reference cell whenever you need to retrieve a price for a different product.
See screenshot:
As you can see in the above worksheet, cell D2 is assigned as the lookup value with two concatenated VLOOKUP wildcards that replace the characters before and after the product code. This means that when you change the content in cell D2, the result for the formula also will change accordingly.
See screenshot:
Example 4: Omitting a single character in a search term
This example is designed to illustrate how the question mark wildcard symbol works with the VLOOKUP function.
In real cases, the most used and popular wildcard symbol is the asterisk (*). This is because it is much more flexible and can do all that the question mark can do and more. So, understanding the asterisk wildcard will add a great deal of flexibility to the question mark.
Despite this, let’s take a look at how the question mark as a wildcard symbol is used in VLOOKUP.
The first thing you need to know is that the question mark wildcard is used to stand for only a single character within a cell’s content. 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 in lookup value.
Let’s take the following example into consideration:
=VLOOKUP(“SACL005?”,Employee_Info,4,FALSE)
This formula has a product code as its lookup value with the question mark wildcard as a suffix.
See screenshot:
The lookup value in the above formula will search for a product code by omitting the last character or letter in the code. Thus, instead of searching for a product code of SACL005L, the formula will search for a product code of SACL005, omitting the last character which is L.
You can use the question mark several times within the same formula to replace as many characters as you like. For instance, if you want to search for a product code without the first three characters, all you need to do is replace those with the question mark.
Such a formula will look like this:
=VLOOKUP(“???L005?”,Employee_Info,4,FALSE)
In this formula, you notice that each of the first three letters is being replaced by a question mark. This means that the number of characters you want to replace will determine the number of question mark wildcard you will use.
See screenshot:
Now let me show you why the asterisk wildcard is popular and why it is preferred that the question mark.
In the above example, to replace the first three characters, the formula uses the question mark symbol three times. However, to perform the same task with the asterisk symbol, you only need to use the wildcard symbol once to replace as many characters as you please. This makes the asterisk wildcard convenient to use and therefore preferred by most.
Example 5: avoiding wildcard symbols in search
Let’s assume your data has a cell with the question mark as part of the actual values of that cell. Say you want to search for that cell ending with this question mark.
With this task, let’s try what we’ve learned in the previous examples. It was said that if you want to search for a cell whose content ends with a particular character or characters, prefix those character(s) with the asterisk (*) wildcard symbol.
In the example below, let’s prefix the question mark (?) symbol and see what happens.
See screenshot:
You notice that the formula didn’t return the correct result. It ignored the cell that fits our search term and goes for the first cell in the lookup column. This is because Excel recognizes the question mark as a wildcard character and not a normal cell value.
Thus, if you are searching for any character which is a wildcard symbol, you have to tell Excel not to treat it as a wildcard but just a normal cell value. To do that, you need to type a tilde (~) before the wildcard. That being said, let’s try this and see our result this time.
See screenshot:
Now we got the correct result.
Because of the tilde (~) wildcard, Excel don’t see the question mark as a wildcard any more, so it doesn’t treat it as one.
This means that the tilde (~) wildcard is used to override the effect of the other two wildcards so that Excel can see them as normal cell values.
Bonus Example: VLOOKUP wildcard and spaces
Sometimes your VLOOKUP formula fails to work because of leading and trailing spaces in the data. These are spaces that comes before and after a cell’s content respectively. This occur most of the time when the data is an imported one.
With the use of Excel wildcard, you can solve this kind of VLOOKUP errors when writing formulas.
Let’s consider the example below:
It is crystal clear that Jordan is on the list of employees. However, the VLOOKUP formula returned an error because it thinks the name (Jordan) we are searching for isn’t available. Why can’t Excel see it when we can?
This is because of extra spaces within the data. It takes more than just the eyes to see these extra spaces. However, once you get rid of these spaces, your formula will work just fine.
See screenshot:
This approach works well when you are dealing with small data. In case of big data, it’ll be a boring approach.
With the use of VLOOKUP wildcard, you can write a formula that will ignore those extra spaces and locate the value you are looking for without problems.
Guess what, the idea you leant in example three can solve this problem. The idea was to use the asterisk wildcard to replace some characters before and after a particular text. In other words, to search for cells containing specific values.
Therefore, let’s refine the above formula to ignore the extra spaces when searching.
See the formula below:
=VLOOKUP(“*Jordan*”,B5:F17,5,FALSE)
As you can see in the first argument of the function, the lookup value is surrounded by two asterisk wildcards. These wildcards are telling Excel to ignore any extra spaces or characters that come either before or after the name Jordan.
With this technique, you can overcome the problems that come with this hard to find leading and trailing spaces.