VLOOKUP Wildcard

VLOOKUP with Wildcard

VLOOKUP helps us tremendously. However, it cannot find or fetch the value if there is just 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 says “ABC,” so VLOOKUP could not recognize these two as the same so end up giving the error result of not available (#N/A). This is the common scenario we all have encountered, isn’t it? However, there is a way to overcome this to a certain extent with the use of wildcard characters in the VLOOKUP function, and in this article, we will show you how to use it.

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

You can download this VLOOKUP Wildcard Excel Template here – VLOOKUP Wildcard Excel Template

#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 fetch the data only if the lookup value is exactly the same as in the table array; otherwise, we will get not available error #N/A.

Follow below steps to use VLOOKUP with wildcard in excel.

  1. For example, look at the below data in excel.

    VLOOKUP Wildcard Example 1
    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 Names”, so we need to look for their respective salary details.

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

    VLOOKUP Wildcard Example 1.1

  3. We get the following result.

    VLOOKUP Wildcard Example 1.2

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

    This 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 doesn’t find the similarity between these two names, so returns an error value as #N/A.
    Similarly, in 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.
    VLOOKUP Wildcard Example 1.4
    So this is how typical VLOOKUP works, but with the usage of wildcards, we can overcome these issues.

#2 – Asterisk (*)

As we know, we have only part of the full name of employees so to match this full name. We are going to 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.

Example #1

For example, look at the below data in excel.

VLOOKUP Wildcard Example 1.5
  • We get the following result.
Example 1.6

Similarly, we have got the remaining values.

VLOOKUP Wildcard Example 1.8

As you can see above, we have used asterisk (*) wildcard character after selecting the employee name cell, so what this does is it will match any number of characters after the selected cell value.

Example #2
  • Now, look at the below example of VLOOKUP.
Example 2.0

The lookup value is “Ramesh,” and in the main table, full name is even though we have used asterisk as the wildcard, it doesn’t return the result.

This is because we have used the asterisk (*) character after the cell address so any value after the cell value will be matched but in this case value is “Ramesh” but this is the middle name, so as of now 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 wildcard character asterisk before and after the lookup value, so the below image shows how to include wildcard character.
VLOOKUP Wildcard Example 2.4
  • We get the following result.
VLOOKUP Wildcard Example 2.3

As we can see above, we have included an asterisk (*) wildcard character before selecting the lookup value cell and also included after selecting the wildcard character. So the first asterisk will match the name as “Sachin,” and the last asterisk will match the name as “Tendulkar,” and 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.

  • For example, look at the below data.
Example 3.0

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

  • Let’s apply the VLOOKUP function.
VLOOKUP Wildcard Example 3.2
  • We get the following result.
Example 3.1

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

  • Wildcard characters are useful but not recommended to use because it can go wrong horribly if not noticed.
  • Asterisk (*) can match any number of characters, but question mark (?) match only one character where they placed.

Recommended Articles

This has been a guide to VLOOKUP Wildcard. Here we discuss the 2 types of excel VLOOKUP wildcard characters (Asterisk (*), Question Mark(?)) along with examples and a downloadable excel template. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion