How to VLOOKUP Partial Text in Excel (With Alternatives)

Partial Text VLOOKUP

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.

Read more: VLOOKUP Partial Text from a Single Cell in Excel

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.

Read more: How to Perform VLOOKUP with Wildcard in Excel

VLOOKUP Function Partial Text Match with a Wildcard

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

Formula using VLOOKUP function

Step 2: Now enter any last keywords of the Brand any press Enter

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:

=VLOOKUP(C17&”*”,B4:E15,4,FALSE)

Formula Using VLOOKUP function

Step 2: Now enter any first keywords of the Brand any press Enter

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:

=VLOOKUP(“*”&C17&”*”,B4:E15,2,FALSE)

For C21:

=VLOOKUP(“*”&C17&”*”,B4:E15,3,FALSE)

For C22:

=VLOOKUP(“*”&C17&”*”,B4:E15,4,FALSE)

Formula using VLOOKUP function

Step 2: Now enter any first or last keywords of the Brand any press Enter

Now enter any first or last keywords of the Brand any press Enter

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.

Read more: VLOOKUP Partial Match Multiple Values

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)

Enter formula using VLOOKUP formula

Step 2: Now enter any keyword in the search box and press Enter

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.

Partial Text VLOOKUP

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

Enter formula using INDEX and MATCH function

Step 2: Now any keyword of the Model and press Enter

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.


Further Readings:

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo