VLOOKUP Partial Match

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.

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

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

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.

Example #1

For example, look at the below data in Excel.

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.

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

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.

• Now, look at cell E4. We have the full company name "Corporate Bridge Consultancy Pvt Ltd" value of $57,771.

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.

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

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.

Now, press Enter key.

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

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

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