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

Get FREE Advanced Excel Exercises with Solutions!

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,

=VLOOKUP("*"&F5&"*",C5:D14,2,FALSE)

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)

Search for Starting Words or Characters Inserting VLOOKUP with Wildcard

  • 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.

Search for Starting Words or Characters Inserting VLOOKUP with Wildcard

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-

=VLOOKUP(F5&"*",C5:D14,2,FALSE)

Where,

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

Find Ending Words or Characters Applying VLOOKUP with Wildcard

  • Press ENTER to get the result.

Find Ending Words or Characters Applying VLOOKUP with Wildcard

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-

=VLOOKUP("?KB002",B5:D14,3,FALSE)

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

Identify a Cell Containing Specific Letter Using VLOOKUP with Wildcard

  • Press ENTER to get the result.

Identify a Cell Containing Specific Letter Using VLOOKUP with Wildcard

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


Similar Readings


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-

=VLOOKUP("*"&F5&"*",C5:D14,2,FALSE)

  • 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.

Find Cell Containing Multiple Letters with Wildcards

  • 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-

=VLOOKUP(F5,C5:D14,2,FALSE)

Avoid Wildcard Symbols in Cells Merging with Wildcards

  • 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-

=VLOOKUP(F5&"*~",C5:D14,2,FALSE)

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

Avoid Wildcard Symbols in Cells Merging with Wildcards

  • 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-

=VLOOKUP("???"&F5,B5:D14,3,0)

Performing VLOOKUP with Wildcard to Find Partial Match

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

Performing VLOOKUP with Wildcard to Find Partial Match

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

Asikul Himel
Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo