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

We will find the salary of Harper using VLOOKUP with a 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: How to Use VLOOKUP to Find Partial Text from a Single Cell


1.2. Finding End Words or Characters

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

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


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.

Read More: How to Use Excel VLOOKUP to Find the Closest Match


2.2. Considering Cells with Wildcard Symbols

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 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: Use VLOOKUP to Find Multiple Values with Partial Match 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


<< Go Back to VLOOKUP Partial Match | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo