VLOOKUP Partial Match

Updated on April 23, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Partial Match With VLOOKUP?

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

For example, consider the below data.

For example, consider the below data

We can find the result using VLOOKUP Partial match as shown in the below image.

find the result using VLOOKUP Partial match

So in this article, we will guide you through the partial match.

Key Takeaways

  • The VLOOKUP function retrieves data from one table if the lookup value matches the one in the lookup table. To avoid errors caused by small spaces or extra characters, a partial match of the lookup value technique is used.
  • The VLOOKUP formula in Excel is a tool that searches for a specific value in a table and returns it to another cell within the same table, but partial matches can be problematic.
  • The asterisk (*) can match any number of characters, depending on the location where it is included in the wildcard.

How Does Partial Match In VLOOKUP 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.

Let us learn how to use VLOOKUP partial match in Excel.

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

Examples

Example #1

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.

Let us use the same example and learn how to find VLOOKUP partial match.

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

 

Example #2

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

Example #3

Consider the below example showing products and sales of a showroom.

VLOOKUP Partial Match - Example 3

To begin with, open the VLOOKUP function first.

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

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

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

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

So, the formula appears as shown in the below image.

Example 3 - Formula

Now, press Enter key.

We can see the result as shown in the below image.

result as shown in the below image

Wildcard Character With VLOOKUP

The important thing is the wildcard asterisk (*).

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

Important Things To Note

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

Frequently Asked Questions

1. Can you use VLOOKUP for partial match Google Sheets?

When you don’t know the entire lookup value (search_key), you can use wildcard characters like question mark (?) to match any single character and asterisk (*) to match any sequence of characters.

2. How does VLOOKUP partial match work?

To match the partial value of the full value, combine wildcard characters with the lookup_value, using an asterisk (*) that matches any number of characters. Open the VLOOKUP function first.

3. How do I remove partial match duplicates in Excel?

To eliminate partial duplicates based on key columns, select only those columns. If your table has numerous columns, click the ‘Unselect All’ button and then select the columns you want to check for duplicates.

Download Template

This article must be helpful to understand the Vlookup Partial Match, with its formula and examples. You can download the template here to use it instantly.

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: –