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.

**Table of Contents**hide

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

**=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.

**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.