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

`=VLOOKUP("*"&C18,B5:E16,1,FALSE)`

For cell **C21**:

`=VLOOKUP("*"&C18,B5:E16,2,FALSE)`

For cell **C22**:

`=VLOOKUP("*"&C18,B5:E16,3,FALSE)`

For cell **C23**:

`=VLOOKUP("*"&C18, B5:E16,4, FALSE)`

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

`=VLOOKUP(C18&"*",B5:E16,1,FALSE)`

For cell **C21**:

`=VLOOKUP(C18&"*",B5:E16,2,FALSE)`

For cell **C22**:

`=VLOOKUP(C18&"*",B5:E16,3,FALSE)`

For cell **C23**:

`=VLOOKUP(C18&"*",B5:E16,4,FALSE)`

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

**=VLOOKUP(“*”&C18&”*”,B5:E16,1,FALSE)**

For cell **C21**:

`=VLOOKUP("*"&C18&"*",B5:E16,2,FALSE)`

For cell **C22**:

`=VLOOKUP("*"&C18&"*", B5:E16,3,FALSE)`

For cell **C23**:

`=VLOOKUP("*"&C18&"*",B5:E16,4,FALSE)`

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.

`=VLOOKUP("*"&C18&"*", B5:E16,4, FALSE)`

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.

`=INDEX($C$5:$C$16, MATCH("*"&C18&"*",$C$5:$C$16,0))`

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

`=LEFT(VLOOKUP(E5,$B$5:$C$14,2,FALSE),5)`

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.