# How to VLOOKUP Partial Text in Excel (With Alternatives) 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.

## 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. 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:

=VLOOKUP(C17&”*”,B4:E15,4,FALSE) 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:

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

For C21:

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

For C22:

=VLOOKUP(“*”&C17&”*”,B4:E15,4,FALSE) 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.

Read more: VLOOKUP Partial Match Multiple Values 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.
• 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. 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.  