VLOOKUP Wildcard

Last Updated :

21 Aug, 2024

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is VLOOKUP With 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.

NA error - Vlookup Wildcard

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.

VLOOKUP Wildcard characters
Table of contents
  • VLOOKUP Wildcards are the Asterisk (*) and the Question Mark (?) characters that are inserted in a VLOOKUP formula to fix the “#NA” error that occurs when there is a difference in the lookup_value from the lookup_table.
  • In the wildcard characters we use, the asterisk matches any number of characters, and the question mark (?) will match only one character where they are placed.
  • The wildcard characters must be used within double quotes in the formula, and in the place where we enter the lookup_value. Else, we will get an error, or the required result will not be retrieved.

How To VLOOKUP Wildcards (*, ?) In Excel?

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.


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


    VLOOKUP Wildcard Example 1.1

  3. We get the following result.


    VLOOKUP Wildcard Example 1.2

  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.
    VLOOKUP Wildcard Example 1.4

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.

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 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.
Example 2.0

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.
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 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.
Example 3.0

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

Important Things To Note

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

Frequently Asked Questions

1. What is the purpose of VLOOKUP with Wildcard?

VLOOKUP helps us get the desired details using the lookup_value. When there is a sight change in the lookup_value, we won’t get the required details but an error. To overcome this, we insert the wildcard characters in the formula.

2. What are the different wildcard characters in a VLOOKUP formula?

The two wildcard characters to use with the VLOOKUP formula are,
1. Asterisk (*) – A wildcard character that matches any number of characters after the selected cell value.
2. Question Mark (?) - It is a wildcard character that will match only one character after the selected cell value.

3. What are the reasons why the VLOOKUP Wildcard might not work?

A few reasons why a VLOOKUP formula may not work are,
• The lookup_value is different from the value in the lookup_table.
• The column of the lookup_value is on the left of the lookup_table.
• A new column is inserted, or an existing column’s position is changed or deleted from the lookup_table.

Download Template

This article must help understand VLOOKUP Wildcard with its formulas and examples. You can download the template here to use it instantly.

VLOOKUP Wildcard Excel TemplateDownload

This article is a guide to VLOOKUP Wildcard. Here we discuss two wildcard characters - Asterisk (*) & Question Mark(?), examples & downloadable excel template. You may also look at these useful functions in Excel: -