Md. Abdullah Al Murad
The partial text means using some letters or groups of letters instead of the full text. In MS Excel, instead of searching for something with full text, we may have to use partial text. Excel provides various ways for this issue where the lookup value will be returned based on the partial match. Whenever you deal with a large dataset, you need to find a specific value. You can search for it using the partial text. In this article, we are going to show different ways to find partial text matches using the VLOOKUP function.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
2 Ways to Vlookup Partial Text Match Using the VLOOKUP Function in Excel
Here, we have taken a dataset of some Products Information, their Brand, Model, ID, and Unit price. We want to look for a specific value. For doing this, we use the partial text that matches the specific text. We have used the VLOOKUP function and wildcard to complete the operation.
Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.
1. Applying VLOOKUP Function for Partial Text Match with Wildcard
Here, we will search for a portion of text, in other words, the partial text. For doing this, we use the wildcard to find out all the details of a specific product.
1.1 First Partial Text Match with Wildcard (“*”&Text)
Firstly, we will see how we can match any number of characters before the start of the text. To do this, we created formulas for cells ranging from C20 to C23. Put the following formula in the respective cell.
For cell C20:
For cell C21:
For cell C22:
For cell C23:
Formula Explanation:
- The “*” (asterisk) symbol is used to match the data with our wildcard characters. The “&” (ampersand) symbol is used to concatenate the asterisk and our entered keyword.
- B5:E16 is the range where we will search the data.
- 1, 2, 3, and 4 are used to define column numbers.
- FALSE is to define the exact match.
Finally, you get the results shown in the image below after pressing ENTER.
Read More: How to Perform VLOOKUP with Wildcard in Excel (2 Methods)
1.2 Last Partial Text Match with Wildcard (Text&“*”)
Here we will be looking for a match of any number of characters after the start of the Brand name. Again, enter the following formulas in cells C20 to C23.
For cell C20:
For cell C21:
For cell C22:
For cell C23:
Consequently, press ENTER, and you get the result (see the image below).
1.3 Partial Text Match Anything Between Wildcards (“*”&Text&“*”)
In this part, we search for products using keywords that could be the first and last parts of the product. From C20 to C23 cells, we enter the following formulas.
For cell C20:
For cell C21:
For cell C22:
For cell C23:
Subsequently, you will get the result shown in the image below.
Read More: VLOOKUP with Wildcard in Excel (3 Methods)
Similar Readings
- Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel
- VLOOKUP to Search Text in Excel (4 Easy Ways)
- VLOOKUP Partial Match Multiple Values (3 Approaches)
- VLOOKUP Function in Excel: Learn with Examples
2. Using the VLOOKUP Function for Partial Text 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 as above, and the formula is closely similar to part method 1.3. Our task is to find the unit price using search keywords.
Steps:
- Firstly, move to cell C20 and insert the formula.
As we can see, here we have entered the partial text as MS in cell C18, which is the middle two characters of “SAMSUNG” in the B5:E16 lookup array. The 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.
Finally, the result will look like the image below.
Read More: VLOOKUP with Multiple Matches in Excel
Alternative Way: Using INDEX-MATCH Formula to Vlookup Partial Text
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 combination of the INDEX and MATCH functions. These functions find out the value for the partial match. Follow the steps.
Steps:
- Initially, go to the C20 cell and write up the formula.
Formula Explanation:
- First, let’s see the inner function which is MATCH. In the first argument “*”&C18&”*” this matches the data with our partial text in the Model column. $C$5:$C$16 this is the Model column range. 0 is used to define the exact match.
- Then in the INDEX function, $C$5:$C$16 is the range where we will find the index. The return result of the MATCH function data will be considered as a row number.
Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)
Employing the VLOOKUP Function to Extract Partial Text
Unlike the previous methods, you might need to extract partial text characters. Luckily, you can do it simply by using the VLOOKUP function. Suppose you have a dataset of IDs along with their Names. Now, you want to know the first five characters of the Names. See the dataset we have attached here for better visualization. We have used the LEFT function with the VLOOKUP function to find out the first five characters.
Steps:
- First of all, go to cell F5 and insert the following formula.
Here,
E5= The Value to Look For.
$B$5:$C$14= The Lookup Array.
Formula Explanation:
VLOOKUP(E5,$B$5:$C$14,2, FALSE) → finds the lookup of reference E5 cell where $B$5:$C$14 is the range we want to look for it. We want to get the exact same match that’s why we have chosen FALSE for the exact match. We also take the col_index_num as 2.
- Output → 1524351
LEFT(VLOOKUP(E5,$B$5:$C$14,2, FALSE),5) → returns the 5 characters at the left of the matched value of 1524351
- Output → Frede.
Eventually, after pressing ENTER and dragging it down, you will get the following result, like the image below.
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 of searching anything using “GALAXY”. |
| Asterisk (*) Matching Issue | An 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”. The VLOOKUP function will return this error if the search item is missing. |
| #N/A Error in INDEX/MATCH | If the data is not found by the INDEX or MATCH functions, then this error will be returned. |
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to use the VLOOKUP function for partial text in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, Exceldemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.
” title=”