What is Partial Match with VLOOKUP
We hope you already have good knowledge about VLOOKUP function, so it can fetch the data from one table to another only if the lookup value is same as the one in the lookup table, so even a small space or extra characters will lead to not available (#N/A) error. So because of one small space or extra character, we end up getting error value so to avoid these cases we can use the technique of partial match of lookup value with VLOOKUP function, so in this article, we will guide you through the partial match.
How does it work?
If at all if you want the VLOOKUP function to work properly you need to have a proper lookup value which are same in the table as well as in formula cell, otherwise we will end up getting errors.
For example, look at the below data in excel.
We have two tables with the first table we have Co., Name and Invoice Value and in the second table we have only Co., Name but there is no invoice value.
However, when we look at the second table we don’t have the same lookup values as we have in the first table. For example, look at the 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 match of the company so it returns an error.
- This 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.
Examples of Partial Match with VLOOKUP
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 to be combined with lookup value. So, wildcard character that we need to use is an asterisk (*) so this wildcard match any number of characters.
- 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 enter the ampersand (&) symbol and select the cell address as D2.
- Now again enter ampersand symbol and enter the wildcard character asterisk (*).
- After doing the above as usual finish the VLOOKUP function by entering the remaining parameters.
There we have the results.
- Now look at the cell E4 in this we have the value of the full company name “Corporate Bridge Consultancy Pvt Ltd” value of 57,771.
Say thanks to wildcard character asterisk (*).
- Now you must be wondering how this has worked out, let me explain you the concept here for you.
- We have combined wildcard character asterisk (*) with the cell address, so this reads like this.
- 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 wildcard character cannot match this and returns the error as “Not Available” (#N/A).
- So full name should be there in the main table and even if the partial name is there in the lookup excel table our wildcard character matches the partial word in the lookup table.
How Does Wildcard Character work?
Here the important thing is wildcard asterisk (*) it work in the below logic.
- “*”&Corporate: This matches any number of characters before the start of the word “Corporate”.
- Corporate&“*”: This matched any number of characters after the start of the word “Corporate”.
- “*”&Corporate&“*”: This matched any number characters before & after the start of the word “Corporate”.
Things to Remember
- Partial Match is dangerous because “Michael Clarke” and “Michael Jordon” are considered as same if the lookup value is just “Michael” and combined with wildcard characters.
- Asterisk (*) matches any number of characters and matching depends on where we include this wildcard.
This has been a guide to VLOOKUP Partial Match. Here are some examples of partial match with VLOOKUP and how the wildcard character work and downloadable excel template. You may also look at these useful functions in excel –