How to Perform VLOOKUP with Wildcard in Excel (3 Methods)

Whenever you are handling a wide range of data in your worksheet, applying VLOOKUP with wildcards is an effective way to find your texts or values quickly in Microsoft Excel. The VLOOKUP function with wildcard will help you to return your desired value even if you have remembered the partial fractions of it. Today in this article we will discuss how to perform VLOOKUP with wildcards in Excel.

Consider a situation where you are given ID, Salary, and Name of some sales rep. Now you have to find a specific salary of a specific sales representative using the VLOOKUP function with wildcards. We will learn two ways to complete the task.

1. Using VLOOKUP with Single Wildcard in Excel

Applying the VLOOKUP function with wildcards, we can find any single letter, word, or value. Now create two sections in Column F in your worksheet named Search For and Result where we will find out the result using VLOOKUP with wildcards. We will discuss three different ways to do that. Let’s learn!

1.1. Searching for Starting Words or Characters Using VLOOKUP with Wildcard Character

We will find the salary of Harper using VLOOKUP with wildcard. The full name is not given.

Steps:

• First, in cell F7, apply VLOOKUP with wildcards. Insert the values and the final form is,

Where,

• Cell (F5) is the lookup value. We use this Asterisk ( * ) sign as a wildcard that matches zero or more characters. This asterisk (*) joins the text in the named range value with a wildcard. The ampersand (&) is used to concatenate.
• Lookup_Array is C5:D14
• Col_index_num is 2
• We want the EXACT match (FALSE)

• Press ENTER to find the result.
• So we have found the salary of Alex Harper using just the last part of his name by applying VLOOKUP with wildcards.

Read More: Excel Match Wildcard in Lookup Array (with 3 Formulas)

1.2. Finding End Words or Characters Using VLOOKUP with Wildcard Character

VLOOKUP with wildcard is also used to find any ending words or characters of your original text.

Steps:

• To find the ending words or characters from your dataset, apply this formula-

Where,

• The lookup value is F5&”*”. The asterisk sign ( * ) is used to find the ending words and the ampersand ( & ) is used to concatenate them.

• Press ENTER to get the result.

We have successfully got the result using VLOOKUP with wildcard.

1.3. Identifying a Cell Containing Specific Letter Using VLOOKUP with Wildcard Character

Here we are given the word “BKB002” to search and return the Salary of this ID. The last letter is missing from this ID. To Identify any specific letter from a word we can use VLOOKUP with wildcards.

Steps:

• In that case, the formula is-

• The lookup_value of this formula is ?KB002.Where the question mark (?) is used as a wildcard to return the exact same value.

• Press ENTER to get the result.

Read More: Wildcard with VLOOKUP in Excel (6 Examples)

Similar Readings

• How to Use VLOOKUP in VBA in Excel (4 Ways)
• VLOOKUP Fuzzy Match in Excel (3 Quick Ways)
• How to Use Partial VLOOKUP in Excel (5 Suitable Examples)
• Excel VBA: Delete Files with Wildcards (4 Methods)
• How to Use SUMIFS Function with Wildcard in Excel (3 Examples)

2. Applying VLOOKUP with Multiple Wildcards in Excel

Multiple wildcards are used to return multiple letters or values. Let’s learn this method in two different situations.

2.1. Finding Cell Containing Multiple Letters with Wildcards

Let’s say we are given a middle name Gordon of a sales and we have to find his salary using VLOOKUP with multiple wildcards.

Steps:

• To do that insert the VLOOKUP with multiple wildcards formula-

• Here “*”&F5&”*”is the lookup value. We used an asterisk and ampersand sign on both sides of the cell reference to match missing words on both sides.

• Press ENTER to get the result. So we have successfully got the value using VLOOKUP with multiple wildcards.

2.2. Considering Cells with Wildcard Symbols Using VLOOKUP Function

There are many situations when we need to nullify the effect of wildcards. In the following example, the asterisks in the dataset are not used as a wildcard but used as a part of the word. Let’s say we need to find the salary of John*.

Step 1:

• To do that we used this formula-

• And press ENTER to get the result.

From the result, we can see that the function took the asterisk sign as a wildcard and returned the value of another cell.

Step 2:

• To avoid the wildcard and get the exact result, apply this formula with a special sign-

• The lookup value is F5&”*~”. The tilde (~) will nullify the effect of the asterisk and then the function will return the exact value.

• Press ENTER to get the result.

3. Performing VLOOKUP with Wildcard to Find Partial Match in Excel

Sometimes you might feel the need to find a partial match from a large data table. In that case, you can perform the VLOOKUP function with a wildcard symbol. Follow the instructions below-

Steps:

• First, choose a cell (F7) and write the below formula down-

• Next press ENTER
• Finally, we have successfully extracted a partial match by applying the VLOOKUP function with a wildcard in Excel.

Read More:  How to VLOOKUP and Return Multiple Values Vertically in Excel

Things to Remember

➤ Asterisk (*) can match any number of characters, but the question mark (?) matches only one character where they are placed.

➤ The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function Never searches for the data on the left.

➤ The Asterisk(*) is used as a wildcard. Use it on both sides of the partial match string if you need wildcard characters on both sides.

Download Practice Workbook

Download this practice sheet to exercise the task while you are reading this article.

Conclusion

VLOOKUP with wildcard is quite easy if all signs are perfectly placed. We hope this article proves useful to you. Do comment if you have any questions or queries.

Related Articles

• How to Use Named Range in Excel VLOOKUP Function
• VLOOKUP with Multiple Criteria in Excel (6 Examples)
• How to Find And Replace Values Using Wildcards in Excel
• XLOOKUP vs VLOOKUP in Excel (Comparative Analysis)
• How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)
• INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
• How to Apply Double VLOOKUP in Excel (4 Quick Ways)