Vlookup Wildcard How To Use Vlookup Wildcards

VLOOKUP with Wildcard

VLOOKUP helps us tremendously. However, it cannot find or fetch the value if there is a slight difference in the lookup value. For example, in the main table, the value says “ABC Company.” But in the lookup table, it just shows “ABC,” so VLOOKUP could not recognize these two as the same and ended up giving the error result of not available (#N/A). Unfortunately, it is a common scenario we all have encountered. However, there is a way to overcome this to a certain extent with wildcard characters in the VLOOKUP function. In this article, we will show you how to use it.

How to VLOOKUP Wildcards (*, ?) in Excel?

#1 – Typical VLOOKUP Function

VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more fetches the data only if the lookup value is the same as in the table array; otherwise, we will not get available error #N/A.

#2 – Asterisk (*)

As we know, we have only part of the full name of employees, so to match this full name. We will combine asterisk (*) wildcard in excelWildcard In ExcelIn Excel, wildcards are the three special characters asterisk, question mark, and tilde. Asterisk denotes multiple characters, a question mark denotes a single character, and a tilde denotes the identification of a wild card character.read more with the VLOOKUP function.

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. Let us apply the VLOOKUP function and see how many employees we get the salary details. We get the following result. We have got two error values in cells F4 and F8. It is because, in cell F4, we have the employee name as “Shruthi,” but in “Emp Table, “actual name is “Shruthi Naidu” (cell A3), so VLOOKUP does not find the similarity between these two names, so returns an error value as #N/A.Similarly, in the E8 cell, we have a value of “Abhishek, “but in “Emp Table, “the actual name is “Abhishek Sharma” (cell A4), so we have an error value.So, this is how a typical VLOOKUP works. But, with the usage of wildcards, we can overcome these issues.

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.

It is because, in cell F4, we have the employee name as “Shruthi,” but in “Emp Table, “actual name is “Shruthi Naidu” (cell A3), so VLOOKUP does not find the similarity between these two names, so returns an error value as #N/A.Similarly, in the E8 cell, we have a value of “Abhishek, “but in “Emp Table, “the actual name is “Abhishek Sharma” (cell A4), so we have an error value.So, this is how a typical VLOOKUP works. But, with the usage of wildcards, we can overcome these issues.

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 name cell so that it will match any number of characters after the selected cell value.

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.

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

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

If you look at the bill numbers in the first table, we have the first three characters as alphabets and next 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.

Things to Remember Here

The wildcard characters are useful but not recommended because they can go wrong horribly if not noticed.Asterisk (*) can match any number of characters, but a question mark (?) matches only one character where they are placed.

VLOOKUP with Wildcard#

How to VLOOKUP Wildcards (*, ?) in Excel?#

#1 – Typical VLOOKUP Function#

#2 – Asterisk (*)#

#3 – Question Mark (?)#

Things to Remember Here#

Recommended Articles#