How to Perform VLOOKUP with Wildcard in Excel (2 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. 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.


Download Practice Workbook

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


2 Easy Methods to Perform VLOOKUP with Wildcard 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.

Search for Starting Words or Characters Inserting VLOOKUP with Wildcard


1. Apply 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 naming 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!

Search for Starting Words or Characters Inserting VLOOKUP with Wildcard


i. Search for Starting Words or Characters Inserting VLOOKUP with Wildcard

Step 1:

  • We will find the salary of Harper using VLOOKUP with wildcard. The full name is not given. Now in cell F6, apply VLOOKUP with wildcards. Insert the values and the final form is,
=VLOOKUP("*"&F4&"*",C4:D13,2,FALSE)
  • Where F4 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 C4:D13
  • 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.

Search for Starting Words or Characters Inserting VLOOKUP with Wildcard

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


ii. Find Ending Words or Characters Applying VLOOKUP with Wildcard

Step 1:

  • VLOOKUP with wildcard is also used to find any ending words or characters of your original text. To find the ending words or characters from your dataset, apply this formula,
=VLOOKUP(F4&"*",C4:D13,2,FALSE)
  • Here the lookup value is F4&”*”. The asterisk sign ( * ) is used to find the ending words and the ampersand ( & ) is used to concatenate it.

 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


iii. Identify a Cell Containing Specific Letter Using VLOOKUP with Wildcard

Step 1:

  • Here we are given the word “BKB006L” 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. In that case, the formula is,
=VLOOKUP("BKB006L?",B4:D13,3,FALSE)
  • The lookup_value of this formula is BKB006L?. 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


Similar Readings


2. Insert 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.

i. Find Cell Containing Multiple Letters Using VLOOKUP with Wildcards in Excel

Step 1:

  • 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.
  • To do that insert the VLOOKUP with multiple wildcards formula.
=VLOOKUP("*"&F4&"*",C4:D13,2,FALSE)
  • Here “*”&F4&”*” 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 Using VLOOKUP with Wildcards in Excel

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

Find Cell Containing Multiple Letters Using VLOOKUP with Wildcards in Excel


ii. Avoid Wildcard Symbols in Cells Merging VLOOKUP with Wildcards in Excel

Step 1:

  • 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*. To do that we used this formula
=VLOOKUP(F4,C4:D13,2,FALSE)

 Avoid Wildcard Symbols in Cells Merging VLOOKUP with Wildcards in Excel

  • And press Enter to get the result.

 Avoid Wildcard Symbols in Cells Merging VLOOKUP with Wildcards in Excel

Step 2:

  • From the result, we can see that the function took the asterisk sign as a wildcard and returned the value of another cell.
  • To avoid the wildcard and get the exact result, apply this formula with a special sign.
=VLOOKUP(F4&"*~",C4:D13,2,FALSE)
  • The lookup value is F4&”*~”. The tilde (~) will nullify the effect of the asterisk and then the function will return the exact value.

 Avoid Wildcard Symbols in Cells Merging VLOOKUP with Wildcards in Excel

  • Press Enter to get the result.

 Avoid Wildcard Symbols in Cells Merging VLOOKUP with Wildcards in Excel

Read More: Excel VLOOKUP to Return Multiple Values Vertically


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.


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.


Similar Articles to Explore

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo