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. , 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 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 is the same in the table as well as in the 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 with being combined with lookup value. So, the 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 the 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, the 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
- A 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 a partial match with VLOOKUP and how the wildcard character work and the downloadable excel template. You may also look at these useful functions in excel –