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.

**Table of Contents**hide

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

**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)`

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

**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)`

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

**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)`

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

**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)`

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

**Step 2:**

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

**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)`

**Step 4:**

- Then press
**ENTER**.

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

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