VLOOKUP Partial Match

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What is Partial Match with VLOOKUP

We hope you already have good knowledge about the VLOOKUP functionVLOOKUP FunctionThe 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, so it can fetch the data from one table to another only if the lookup value is the same as the one in the lookup table. So, even a small space or extra characters will lead to a not available (#N/A) error. We get an error value because of one small space or extra character. To avoid these cases, we can use the partial match of lookup value technique with the VLOOKUP function. So in this article, we will guide you through the partial match.

How does it work?

If you want the VLOOKUP function to work properly, you must have a proper lookup value, the same in the table and the formula cell. Otherwise, we will end up getting errors.

For example, look at the below data in Excel.

VLOOKUP Partial Match - Example 1

We have two tables with the first table. We have “Co., Name,” and “Invoice Value.” In the second table, we have only “Co., Name,” but there is no invoice value.

However, when we look at the second table, we do not have the same lookup values as in the first table. For example, look at cell D4.

VLOOKUP Partial Match - Example 1-1
  • The actual value in cell D4 is “Corporate,” but in the first table in cell A6 the actual name of the company is “Corporate Bridge Consultancy Pvt Ltd.”
  • So in these cases, our default VLOOKUP function cannot find the company’s match, so it returns an error.
  • It is where our partial lookup value comes into the picture, so matching the partial value of the first table is what the particle match is all about.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

VLOOKUP in Excel Video Explanation

 

Examples of Partial Match with VLOOKUP

You can download this Vlookup Partial Match Excel Template here – Vlookup Partial Match Excel Template

As we have seen above, we have the full company name in the first table and only a partial name in the second table. So to match the partial value of the full value, we need to combine wildcard characters with the lookup_value. So, the wildcard character we need to use is an asterisk (*), so this wildcard matches any number of characters.

  1. Open the VLOOKUP function first.


    VLOOKUP Partial Match - Example 1-2

  2. Before we select the lookup value, we need to use the wildcard character asterisk (*), so enter this wildcard in double quotes.


    VLOOKUP Partial Match - Example 1-3

  3. Next, insert the ampersand (&) symbol and select the cell address as D2.


    VLOOKUP Partial Match - Example 1-4

  4. Again, enter the ampersand symbol and the wildcard character asterisk (*).


    VLOOKUP Partial Match - Example 1-5

  5. After doing the above, finish the VLOOKUP function by entering the remaining parameters.


    VLOOKUP Partial Match - Example 1-6
    We have the results.

  • Now, look at cell E4. We have the full company name “Corporate Bridge Consultancy Pvt Ltd” value of $57,771.
Example 1-7

Thanks to the wildcard character asterisk (*).

  • Now, you must be wondering how this has worked out. Let us explain the concept here for you.
  • We have combined the wildcard character asterisk (*) with the cell address, so this reads like this.
  • VLOOKUP(“*” &Corporate&”*”)
  • So, anything before and after the word Corporate is matched by the wildcard character asterisk (*) and supplies the full company name as “Corporate Bridge Consultancy Pvt Ltd.”

Corporate is the first word. We need not include anything before the start of the lookup value but look at the cell.

Example 1-8
  • Even though there is the word “Iphone” in both the values, we still got the error because in the first table. We have only the word “Iphone,” but in the second table, we have a new additional word along with “Iphone” as “Iphone Devices.”
  • So, there is this extra word “Devices” comes, so since there is no word in the first table, the wildcard character cannot match this and returns the error as “Not Available” (#N/A).
  • So, the full name should be in the main table. Even if the partial name is in the lookup Excel table, our wildcard character matches the partial word in the lookup table.

How Does Wildcard Character work?

The important thing is the wildcard asterisk (*) it works in the logic below.

  1. “*” &Corporate: This matches any number of characters before the start of the word “Corporate.”
  2. Corporate& “*”: This matched any number of characters after the beginning of the word “Corporate.”
  3. “*” &Corporate& “*”: This matched any number of characters before and after the start of the word “Corporate.”

Things to Remember

  • A partial match is dangerous because “Michael Clarke” and “Michael Jordon” are considered as same if the lookup_value is “Michael” and combined with wildcard characters.
  • Asterisk (*) matches any number of characters, depending on where we include this wildcard.

This article is a guide to VLOOKUP Partial Match. Here are some examples of a partial match with VLOOKUP, how the wildcard character works, and the downloadable Excel template. You may also look at these useful functions in Excel: –