How to Vlookup Partial Text in Excel (with Alternatives)

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:

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.

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.

Eventually, after pressing ENTER and dragging it down, you will get the following result, like the image below.

Things to Remember 

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.

Further Readings

• VLOOKUP Not Working (8 Reasons & Solutions)
• Excel LOOKUP vs VLOOKUP: With 3 Examples
• Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)
• How to VLOOKUP with Multiple Conditions in Excel (2 Methods)
• VLOOKUP and Return All Matches in Excel (7 Ways)