VLOOKUP Wildcard

VLOOKUP helps users to retrieve values using the lookup_value in a dataset. However, it cannot find or fetch the value if there is a slight difference in the lookup_value. To overcome such scenarios, we use the VLOOKUP with Wildcard characters.

For example, in the main table, the lookup_value is for employees “Shruthi and Abhishek”. But the lookup table shows the full names as “Shruthi Naidu and Abhishek Sharma”. Here, VLOOKUP does not recognize these two as the same, and ends up giving the #N/A error, not available, as shown in the image below.

Then, enter the formula with the VLOOKUP Wildcard characters in the corresponding column to get the correct result, irrespective of the changes in the lookup_value, as shown below.

We can VLOOKUP Wildcards in Excel (*, ?) in three ways, namely,

1. Typical VLOOKUP Function.
2. Asterisk (*)
3. Question Mark (?)

Method #1 – Typical VLOOKUP Function

VLOOKUP fetches the data only if the lookup_value is the same as in the table array, otherwise, we will get a “#N/A” error.

The steps to use VLOOKUP with a wildcard in Excel are,

1. Look at the below data in Excel.

   
   
   We have two data tables named “Emp Table” and “Salary Table”, respectively. In the first table, we have “Employee Name,” “Department,” and “Salary” details. But in the second table, we have only “Employee Name,” so we need to look for their respective salary details.

2. Let us apply the VLOOKUP function and see how many employees we get the salary details.

   
   

3. We get the following result.

   
   

4. We have got two error values in cells F4 and F8.

   It is because, in cell F4, we have the employee’s name as “Shruthi,” but in the “Emp Table”, the actual name is “Shruthi Naidu” (cell A3).
   Similarly, in the E8 cell, we have a value of “Abhishek, but in the “Emp Table”, the actual name is “Abhishek Sharma” (cell A4). Therefore, VLOOKUP does not find the similarity between these two names, and returns an #N/A error.
   

Method #2 – Asterisk (*)

As we know, we have only part of the full name of employees. To match this full name, we will combine the asterisk (*) wildcard in excel with the VLOOKUP function.

Example #1

Look at the below data in Excel.

• We get the following result.

Similarly, we have got the remaining values.

As you can see above, we have used an asterisk (*) wildcard character after selecting the employee’s name cell to match any number of characters after the selected cell value.

Example #2

• Now, look at the below example of VLOOKUP.

The lookup_value is “Ramesh.” In the main table, the full name is even though we have used an asterisk as the wildcard. It does not return the result.

It is because we have used the asterisk (*) character after the cell address so that it will match any value after the cell value. But in this case, the value is “Ramesh,” but this is the middle name, so as of now, an asterisk will match only the last name and return the result as: “Ramesh Tendulkar,” but the actual value to be matched is “Sachin Ramesh Tendulkar.”

• So, to match text values before and after the lookup_value, we need to include a wildcard character asterisk before and after the lookup_value. The image below shows how to have a wildcard character.

• We get the following result.

As we can see above, we have included an asterisk (*) wildcard character before selecting the lookup_value cell and after choosing the wildcard character. So, the first asterisk will match the name “Sachin,” and the last asterisk will match the name “Tendulkar.” So, this will be the combination of “Sachin Ramesh Tendulkar.”

Method #3 – Question Mark (?)

If an asterisk matches any number of characters, then the question mark (?) will match only one character.

• Look at the below data.

In cell D2, we have the bill number as 3340, but in the actual main table, the full bill number value is LAN3340, so VLOOKUP cannot fetch the bill value because it does not find the exact bill value match.

• Let us apply the VLOOKUP function.

• We get the following result.

If you look at the bill numbers in the first table, we have the first three characters as alphabets and the followed by a numerical value. So, we can include three question marks to match the first three characters of the bill number.

So, three question marks “???” matched the first three characters before the bill number, and returned the lookup_value as “LAN3340,” so VLOOKUP does the rest as usual.

• The wildcard characters are useful but not recommended because we may get incorrect results with a slight miss.
• Enter the last argument of the VLOOKUP function, i.e., range_lookup, as “False”, an approximate or an exact match. If we don’t enter any value, then the formula will consider “True”, by default.