Md. Abdullah Al Murad
VLOOKUP function is generally used for searching or finding any elements within a range or table in Excel. We can find any elements with partial matches using the VLOOKUP function. There are several ways to perform this type of operation. In this article, I will show different ways to find anything with the VLOOKUP function for the partial match in Excel.
Download the Practice Workbook
4 Ways to Use VLOOKUP for Partial Match
1. VLOOKUP with Wildcard to get Partial/Exact Match
Let’s assume, we have a dataset of sales information with ID, Name, Joining Date, and Sales of products.
Step 1: Enter the following formulas in cell C16 and press Enter
=VLOOKUP("*"&C14&"*",B4:E11,1,FALSE)
Formula Explanation:
- In the first argument “*”&C14&”*” is the lookup value. Here we are using wildcard characters to check the lookup value.
- B4:E11 this is the range where we will search the value.
- 1 is used as we want to extract the data from the first column.
- FALSE is used to define the exact match.
- For more information about the VLOOKUP function, you can check this link
Step 2: Now enter any keyword in the search box and press Enter
2. Retrieve Data Using VLOOKUP Partial Match
In the first part, we have extracted only one value that is Name. Now here we will find out the Name and Joining Data of the searched keyword.
Step 1: Enter the following formula in cell C17 and press Enter
=VLOOKUP("*"&C14&"*",B4:E11,3,FALSE)
Formula Explanation
- This formula is similar to the previous one. Just the main difference is that as we want to extract Joining Date from the 3rd column, that’s why 3 is given as the column index.
Step 2: Now enter any keyword in the search box and press Enter
3. Get Partial Match of Numerical Data with VLOOKUP
Up till now, we have extracted only Name and Joining Date from the given dataset. Now in this section, we will find out the sales for the matched names.
Step 1: Enter the following formula in cell C18 and press Enter key
=VLOOKUP("*"&C14&"*",B4:E11,4,FALSE)
Formula Explanation
- This formula is the same as the previous one. Just the main difference is that as we want to extract Salary from the 3rd column, that’s why 4 is given as the column index.
Step 2: Now enter any keyword in the search box and press Enter
4. Search Data with VLOOKUP for Partial Match and Condition
Let’s see how we can search any information with VLOOKUP for partial matches and a condition. Again, we will be using the same dataset. Our task is to check if the entered keyword matches name has the highest sales or not. If the matched keyword name has the highest sales, then it will print yes, otherwise no.
Step 1: Enter the following formula in cell C17 and press Enter
=IF(VLOOKUP(C16,B4:E11,4)>=E15,"Yes","No")
Formula Explanation
- VLOOKUP(C16, B4:E11,4)>=E15 this is the logical condition of the IF function here. We are checking here if the entered name has the highest sales or not.
- If the entered name’s salary is matched with our already defined highest salary, then it will return “Yes”, otherwise “No”.
- To learn more about the IF function you can check this link
Step 2: Now enter any keyword in the search box and press Enter
Alternative Option
There is an alternative option for the VLOOKUP function in Excel, which is the INDEX function. We can easily find anything by entering partial matches using the formula combination of INDEX and MATCH functions.
Excel Lookup Partial Text Match Using INDEX MATCH
Now we will find out the full name by searching for keywords with INDEX and MATCH functions. Again the dataset will be the same.
Step 1: Enter the following formula in cell C16 and press Enter
=INDEX($B$4:$B$11,MATCH("*"&C14&"*",$B$4:$B$11,0))
Formula Explanation
- First, let’s see the inner function which is MATCH. In the first argument “*”&C14&”*” this matches the data with our partial text in the Model column. $B$4:$B$11 this is the Model column range. 0 is used to define the exact match.
- To explore more about this function, you can visit this link
- Then in the INDEX function, $B$4:$B$11 is the range where we will find the index. The return result of the MATCH data will be considered as a row number.
- To learn more about the INDEX function, you can look at this link
Step 2: Now enter any keyword in the search box and press Enter
Things to Remember
| Common Errors | When they show |
|---|---|
| Match Problem | There might be a matching problem when there are conflicting data in the dataset. Results will be based on the first matched data. |
| Asterisk (*) Matching Issue | If the asterisk (*) sign is not used properly, like at the front, middle, or back based on the requirements. There could be some matching issue. |
| #NA in VLOOKUP/INDEX/MATCH | If the searched value is not present in the given dataset, then all these functions will return this #NA error. |
Conclusion
These are some ways to find any element using partial match in the VLOOKUP function in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.
