VLOOKUP with Wildcard in Excel (3 Methods)

Excel is one of the widely used software applications of our life. We use Excel to make data more meaningful and usable. In this article, we will discuss VLOOKUP with a wildcard. In Excel. VLOOKUP is commonly used to find data from big data sheets or multiple sheets with some clues.

To explain this topic, we used a data set of some students, their ID, and marks in an examination.

Data set for VLLOKUP with wildcard


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to VLOOKUP

VLOOKUP looks up a value in the selected range of cells in the left-most column & returns the value in the same row in the index-number position.

  • Syntax 

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

  • Argument

lookup_value – The value we want.

table_array – The table of data contains information from which we want the output. The table array should contain at least two columns of data. The first column contains the lookup values. These values can be text, numbers, or logical values.

col_index_num – It is the column from which we want value.

range_lookup – Range lookup is a logical value. It instructs VLOOKUP to find a match. The table must be sorted in ascending order. It offers two choices true or false. True for Approximate match and False for Exact match.


Types of Wildcards

We have a total of 3 wildcard characters that are used in Excel.

Asterisk (*) – It searches any number of characters after a text.

Question Mark (?) – This question mark is used to replace with a single character.

Tilde (~) – It can nullify the impact of the above two characters.


3 Methods to VLOOKUP with Wildcard in Excel

We will see the use of three wildcards in Excel here. To get use reference and get results, create 4 cells that are shown on the following image.

1. Excel VLOOKUP with Asterisk

We can use Asterisk wildcard in three different ways in Excel. These are discussed below.

1.1 First Name and an Asterisk

We will use First name and an Asterisk and apply VLOOKUP here.

Step 1:

  • In the search box Cell F5, we take Grant as a reference.

Excel VLOOKUP with Asterisk

Step 2:

  • Write the VLOOKUP formula.
  • In the 1st argument select F5 and Asterisk (*). As we want to lookup is given 1st word and afterward.
  • Select the range C5 to D10 as the table array.
  • Put 2 in the column index, as our required value is in 2 cells right from the matching cell.
  • We want the exact match so select FALSE in the last argument.
=VLOOKUP(F5&"*",C5:D10,2,FALSE)

Excel VLOOKUP with Asterisk

Step 3:

  • Then press ENTER.

We get the outcome after applying the formula. If we want to check, look at the main data set and see that Grant Allen’s outcome is correct.


1.2 Last Name and an Asterisk

We will use Last name and an Asterisk and apply VLOOKUP here.

Step 1:

  • In the search box of Cell F5, we take Kom as a reference.

Excel VLOOKUP with Asterisk

Step 2:

  • Write the VLOOKUP formula.
  • In the 1st argument select Asterisk(*) and F5. As we want to look up last word and previous.
  • Select the range C5 to D10 as the table array.
  • Put 2 in the column index, as our required value is in 2 cells right from the matching cell.
  • We want the exact match so select FALSE in the last argument.
=VLOOKUP("*"&F5,C5:D10,2,FALSE)

Excel VLOOKUP with Asterisk

Step 3:

  • Then press ENTER.

We get the outcome for Asterisk and Last word. We can check the outcome from the data set.


1.3 Middle Name and an Asterisk

We will use a Middle name and an Asterisk and apply VLOOKUP here.

Step 1:

  • In the search box of Cell F5, we take AB as a reference.

Excel VLOOKUP with Asterisk

Step 2:

  • Write the VLOOKUP formula.
  • In the 1st argument select Asterisk(*) and F5 Asterisk(*). As we want to lookup middle word and rest.
  • Select the range C5 to D10 as the table array.
  • Put 2 in the column index, as our required value is in 2 cells right from the matching cell.
  • We want the exact match so select FALSE in the last argument.
=VLOOKUP("*"&F5&"*",C5:D10,2,FALSE)

Excel VLOOKUP with Asterisk

Step 3:

  • Then press ENTER.

We get the outcome for Asterisk, word, and Asterisk combination. We can check the outcome from the data set.


2. Use a Question Mark with VLOOKUP

When we use Asterisk in VLOOKUP it finds a match with any number of characters. But if we want to math with a certain number of characters we need to use question marks. The number of matching characters will depend on the number of question marks we are using. Question will be used on any position of a word.

Step 1:

  • In the search box of Cell F5, we take 001 as a reference.

Question Mark with VLOOKUP

Step 2:

  • Write the VLOOKUP formula.
  • In the 1st argument type one Question(?) sign and F5. As we want to look up one letter before the reference.
  • Select the range B5 to D10 as the table array.
  • Put 3 in the column index, as our required value is in 3 cells right from the matching cell.
  • We want the exact match so select FALSE in the last argument.
=VLOOKUP("?"&F5,B5:D10,3,FALSE)

Question Mark with VLOOKUP

Step 3:

  • Then press ENTER.

 

We can use multiple question marks if we need to find out more than one letter. In this method, we can fix how many letters we want to find out. We can use this question sign at any position of a word and find out another matching too.


3. Nullifying the Effect of Wildcard Characters in Excel VLOOKUP

Let’s assume our data has cells with the Asterisk mark part of the actual values. Now, we want to search for that cell ending with this Asterisk mark.

Step 1:

  • First, modify the data. Use Asterisk mark in data.

Nullifying the Effect of Wildcard Characters in Excel VLOOKUP

Step 2:

  • In the search box of Cell F5, we take Jon*as a reference. Here we want to get the marks of Jon*.

Nullifying the Effect of Wildcard Characters in Excel VLOOKUP

Step 3:

  • Write the VLOOKUP formula.
  • In the 1st argument type and F5. 
  • Select the range C5 to D10 as the table array.
  • Put 2 in the column index, as our required value is in 2 cells right from the matching cell.
  • We want the exact match so select FALSE in the last argument.
=VLOOKUP(F5,C5:D10,2,FALSE)

Nullifying the Effect of Wildcard Characters in Excel VLOOKUP

Step 4:

  • Then press ENTER.

Nullifying the Effect of Wildcard Characters in Excel VLOOKUP

Step 5:

  • Hare, we get the marks of Jony* but we expected Jon*. We have an Asterisk sign at the end of our reference work. We need to nullify the effect of this Asterisk sign. Now, we will modify the reference.

Nullifying the Effect of Wildcard Characters in Excel VLOOKUP

Step 6:

  • Now, apply the formula again. And the formula is:
=VLOOKUP(F5,C5:D10,2,FALSE)

Step 7:

  • Now, press the ENTER.

Finally, we get the desired result. In this way, we need to nullify the effect of a wildcard in Excel. We can also do this in case of a question mark.


Conclusion

Here, we discussed the impact of a wildcard with VLOOKUP in Excel. Using wildcard we can add some conditions. We may also limit the effect of wildcards by using another wildcard. Hope this will help you to get a solution to your problem. Please put your comment on the box if you find it interesting.


Related Links

Alok

Hello, this is Alok. I am a Telecom Engineer. I completed my study at East West University. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo