Md. Abdullah Al Murad
In MS Excel, instead of searching for something with full text we may have to use partial text. Where the lookup value will be returned based on the partial match. Excel provides various ways for this type of issue. In this article, I will show different ways to find partial text matches using the VLOOKUP function.
Download the Practice Workbook
2 Ways to Find Partial Text Match Using the VLOOKUP Function
1. VLOOKUP Function Partial Text Match with a Wildcard
Let’s assume a dataset of mobile products with their Brand, Model, ID, and Unit Price. Now we will find all the details of the product using wildcard characters.
i. “*”&Value
Firstly, we will see how we can match any number of characters before the start of the text.
Step 1: Enter the following formulas in cell C19 to C22
For C19:
=VLOOKUP("*"&C17,B4:E15,1,FALSE)
For C20:
=VLOOKUP("*"&C17,B4:E15,2,FALSE)
For C21:
=VLOOKUP("*"&C17,B4:E15,3,FALSE)
For C22:
=VLOOKUP("*"&C17,B4:E15,4,FALSE)
Formula Explanation
- “*” (asterisk) is used to match the data with our wildcard characters. & (ampersand) is used to concatenate the asterisk and our entered keyword.
- B4:E15 this is the range where we will search the data.
- 1, 2, 3, 4 are used to define column numbers.
- FALSE is to define the exact match.
- For more information, you can visit this link
Step 2: Now enter any last keywords of the Brand any press Enter
ii. Value&“*”
Here we will be looking for a match of any number of characters after the start of the Brand name.
Step 1: Again enter the following formulas in cell C19 to C22
For C19:
=VLOOKUP(C17&"*",B4:E15,1,FALSE)
For C20:
=VLOOKUP(C17&"*",B4:E15,2,FALSE)
For C21:
=VLOOKUP(C17&"*",B4:E15,3,FALSE)
For C22:
Step 2: Now enter any first keywords of the Brand any press Enter
iii. “*”&Value&“*”
In this part, we will search products using keywords that could be the first and past parts of the product.
Step 1: Again enter the following formulas in cell C19 to C22
For C19:
=VLOOKUP("*"&C17&"*",B4:E15,1,FALSE)
For C20:
For C21:
For C22:
Step 2: Now enter any first or last keywords of the Brand any press Enter
2. Vlookup Partial Match Anywhere in the Cell
Here we will see how we can easily find any product from anywhere in the given dataset using the help of a partial match. Again, here we will use the same dataset above and the formula is closely similar to part 3 of method 1. Our task is to find the unit price using search keywords.
Step 1: Enter the following formula in cell C19 and press Enter
=VLOOKUP("*"&C17&"*",B4:E15,4,FALSE)
Step 2: Now enter any keyword in the search box and press Enter
Observation:
As we can see, here we have entered the partial text as “MS”. Which are the middle two characters of SAMSUNG. VLOOKUP function finds the best solution by selecting the first SAMSUNG named column which is the 2nd row of the data. And then the price of this product is returned.
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 text using the formula combination of INDEX and MATCH functions.
Excel Lookup Partial Text Match Using INDEX MATCH
For this part, again we will use the same dataset. Let’s see how to do that:
Step 1: Enter the following formula in cell C19
=INDEX($C$4:$C$15,MATCH("*"&C17&"*",$C$4:$C$15,0))
Formula Explanation
- First, let’s see the inner function which is MATCH. In the first argument “*”&C17&”*” this matches the data with our partial text in the Model column. $C$4:$C$15 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, $C$4:$C$15 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 take a look at this link
Step 2: Now any keyword of the Model and press Enter
Things to Remember
| Common Errors | When they show |
|---|---|
| Match Problem | There are several names where the First or Last name is the same as GALAXY NOTE 10 and GALAXY NOTE 9. In that case, the VLOOKUP function will consider the first data as a result if we search anything using GALAXY. |
| Asterisk (*) Matching Issue | Asterisk (*) matches any number of characters, and matching depends on where we include this wildcard. |
| VLOOKUP and #N/A errors | The #N/A error just means “not found”. If the search item is missing, then the VLOOKUP function will return this error. |
| #N/A Error in INDEX/MATCH | If the data is not found by INDEX or MATCH function then this error will be returned. |
Conclusion
These are some ways to find any element using partial text 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.
