There may arise a situation where you need to perform a partial matching within **VLOOKUP**. To assist you, today I am going to show you how to operate partial matching using the **VLOOKUP **function in Excel. For this session, I am using **Office 365**, feel free to use any version.

**Table of Contents**hide

## Download Practice Workbook

You are welcome to download the practice workbook from the link below.

## Introduction to VLOOKUP Function

**Summary:**

The **VLOOKUP** function looks for a given value in the leftmost column of a given data range and then returns a value in the same row from the specified column.

**Syntax:**

The syntax of the **VLOOKUP** function is:

`=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])`

**Arguments:**

ARGUMENT |
REQUIRED/OPTIONAL |
EXPLANATION |

lookup_value |
Required | The value that it looks for is in the leftmost column of the given table. Can be a single value or an array of values. |

table_array |
Required | The table in which it looks for the lookup_value in the leftmost column. |

col_index_num |
Required | The number of the column in the table from which a value is to be returned. |

[range_lookup] |
Optional | Tells whether an exact or partial match of the lookup_value is required. 0 for an exact match, 1 for a partial match. The default is 1 (partial match). |

**Return Value:**

Returns the value of the same row from the specified column of the given table, where the value in the leftmost column matches the **lookup_value**.

**Available in:**

Excel 365 | Excel 2021 | Excel 2021 for Mac | Excel 2019 | Excel 2019 for Mac | Excel 2016 | Excel 2016 for Mac | Excel 2013, 2010, 2007 | Excel for Mac 2011 | Excel Starter 2010

## 5 Suitable Examples of Using Partial VLOOKUP in Excel

First things first, let’s get to know about the dataset which is the base of our examples. Here I have a table that contains **3 **columns, **Sales Person**, **State**, and **Sales**. Using this dataset, I will operate the partial match in Excel **VLOOKUP**.

Note that this is a basic table to keep things straightforward. In a real-life scenario, you may encounter a much larger and more complex dataset.

For a **partial match**, you will need to use the** asterisk sign (*)** as a

**wildcard**. Any number of characters can take place in place of an asterisk. You will understand more when I will show examples.

### 1. Match Value from Beginning Using Partial VLOOKUP in Excel

You know that the **VLOOKUP** function searches for a value within a given array and produces the result. So, you can provide the value within it to find the match and return the answer.

Here, I am going to search for a name and when the match is found, the formula will return the corresponding **State**. In this section, I will show you how to match a value that is at the** beginning of a string**.

Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want the corresponding
**State**. Here, I selected**Cell C14**. - Secondly, in
**Cell C14**write the following formula.

`=VLOOKUP(C13&"*",$B$5:$D$11,2,0)`

- Thirdly, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

- In the
**VLOOKUP**function, I selected**C13&”*”**as**lookup_value**.**C13**contains the searching value,**Rod**. And, the**asterisk sign**denotes that any number of characters can take place. The`(*)`

**ampersand**sign joins these two. So, the`(&)`

**lookup_value**stands as such, the value can be anything that starts with**Rod**. - Here, the
**table_array**is**$B$5:$D$11**and I have used**2**as**col_index_no**since I need the value from the**2nd**column. **0**is defining that I want to get an**exact match**.

**Read More: [Fixed!] Excel VLOOKUP Partial Match Not Working**

### 2. Apply Partial VLOOKUP to Match Value at End of String

In the earlier section, you have seen how to match the value from the **beginning of the string**. In this section, I will show you how you can use the **VLOOKUP **function for a **partial match** at the **end of the string**. For this example, I will find the corresponding **Sales** for a specific** Last Name**.

Let me show you how to do it.

**Steps:**

- In the beginning, select the cell where you want the
**Sales**. Here, I selected**Cell C14**. - Next, in
**Cell C14**write the following formula.

`=VLOOKUP("*"&C13,$B$5:$D$11,3,0)`

- In the following step, press
**Enter**to get the corresponding**Sales**.

**🔎** **How Does the Formula Work?**

- In the
**VLOOKUP**function, I selected**“*”&C13**as**lookup_value**,**$B$5:$D$11**as**table_array**,**3**as**col_index_num**, and**0**as**range_lookup**. - Here, for the
**lookup_value**I used the**asterisk sign**at first so the string can start with any character. And then, the`(*)`

**ampersand (&)**will join it with the value in**Cell C13**. So, the**lookup_value**can be anything that ends with the value in**Cell C13***.* - The
**table_array**is**$B$5:$D$11**and I have used**3**as**col_index_no**since I need the value from the**3rd**column. **0**is defining that I want to get an exact match.

**Read More:** **How to Perform Partial Match String in Excel (5 Methods)**

### 3. Employ Partial VLOOKUP Irrespective of Position in Excel

If you need to match the value that can be either at the beginning or in the middle or at the end of the string, then this example will be a helpful one. Let’s see how you can do that.

**Steps:**

- Firstly, select the cell where you want to find the match.
- After that, write the following formula in that selected cell.

`=VLOOKUP("*"&C13&"*",$B$5:$D$11,3,0)`

- After that, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

- In the
**VLOOKUP**function, I selected**“*”&C13&”*”**as**lookup_value**,**$B$5:$D$11**as**table_array**,**3**as**col_index_num**, and**0**as**range_lookup**. - Here, for the
**lookup_value**I used the**asterisk sign**both at first and at the end. So, the`(*)`

**lookup_value**can be anything that contains the value in**Cell C13***.* - The
**table_array**is**$B$5:$D$11**and I have used**3**as**col_index_no**since I need the value from the**3rd**column. **0**is defining that I want to get an**exact match**.

**Read More:** **VLOOKUP Partial Text from a Single Cell in Excel**

### 4. Get Multiple Results Using Partial VLOOKUP in Excel

In this example, I will show you how you can use the Excel **VLOOKUP function for a partial match **and get **multiple results**. Here, I will search for a value and then return the corresponding information from the table.

Let’s see the steps.

**Steps:**

- To begin with, select the cell where you want the first match. Here, I selected
**Cell B16**. - Then, in
**Cell B16**write the following formula.

`=VLOOKUP("*"&C13&"*",$B$5:$D$11,{1,2,3},0)`

- Afterward, press
**Enter**to get the result. - If you are using an older version of
**Microsoft Excel**than**Excel 2019**, press**Ctrl**+**Shift**+**Enter**.

**🔎** **How Does the Formula Work?**

- In the
**VLOOKUP**function, I selected**“*”&C13&”*”**as**lookup_value**,**$B$5:$D$11**as**table_array**,**{1,2,3}**as**col_index_num**, and**0**as**range_lookup**. - Here, for the
**lookup_value**I used the**asterisk sign**both at first and at the end. So, the`(*)`

**lookup_value**can be anything that contains the value in**Cell C13***.* - The
**table_array**is**$B$5:$D$11**and I have used**{1,2,3}**as**col_index_no**since I need the value from all the**3**columns. **0**is defining that I want to get an exact match.

### 5. Employ IF and VLOOKUP Functions for Partial Match in Excel

Suppose, you want to know if a **Sales Person** has the highest sales or not then you can use the **VLOOKUP** function for that. So, I will show you how you can use the **VLOOKUP** function for a partial match in Excel to find out If a **Sales Person** has the highest **Sales **or not.

Let me show you the steps.

**Steps:**

- Firstly, select the cell where you want the result.
- Secondly, write the following formula in that selected cell.

`=IF(VLOOKUP("*"&C13&"*",$B$5:$D$11,3,0)=MAX(D5:D11),"Yes","No")`

- Thirdly, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**MAX(D5:D11):**Here, in**the MAX function**, I selected cell range**D5:D11**as**numbers**. The formula will return the largest value in the given range which is the**highest Sales**.**VLOOKUP(“*”&C13&”*”,$B$5:$D$11,3,0):**Now, in the**VLOOKUP**function, I selected**“*”&C13&”*”**as**lookup_value**,**$B$5:$D$11**as**table_array**,**3**as**col_index_num**, and**0**as**range_lookup**. The formula will return the partial match for**C13**from column**3**which is the**Sales**for a specific**Sales Person**.**IF(VLOOKUP(“*”&C13&”*”,$B$5:$D$11,3,0)=MAX(D5:D11),”Yes”,”No”):**Here,**the IF function**will check if the result from the**VLOOKUP**function is equal to the value I got from the**MAX**function. If the**logical_test**is**True**then the formula will return**Ye**s. Otherwise, It will return**No**.

**Similar Readings**

**How to Vlookup Partial Match for First 5 Characters in Excel****Excel Partial Match Two Columns (4 Simple Approaches)****Excel SUMIF with Partial Match (3 Ways)**

## An Alternative to Partial VLOOKUP in Excel

You can use the combination of **INDEX **and **MATCH** functions. This can be a useful alternative to the **VLOOKUP **for a partial match. The **MATCH** function locates the position of a lookup value and the **INDEX** function returns the value from the given location. So, I will use the same example to get the same result using a different formula. Let’s see how you can do that.

**Steps:**

- First, select the cell where you want to find the match. Here, I selected
**Cell C14**. - Secondly, in
**Cell C14**write the following formula.

`=INDEX($C$5:$C$11,MATCH("*"&C13&"*",$B$5:$B$11,0))`

- After that, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**MATCH(“*”&C13&”*”,$B$5:$B$11,0):**Within**the MATCH function**, I have compared the values and returned the position when it was found.**INDEX($C$5:$C$11,MATCH(“*”&C13&”*”,$B$5:$B$11,0)):**Now,**the INDEX function**returns the value according to the location number. Since I want the output from the**State**column, I have provided**$C$5:$C$11**as the array reference within**INDEX**.

**Read More:** **How to VLOOKUP Partial Text in Excel (With Alternatives)**

## Advanced Use of Partial VLOOKUP in Excel

You may find several advanced scenarios to use the **VLOOKUP** function. Here I am mentioning an advanced operation, where I will find all the values corresponding to the matched value.

For example, I have set the criteria value as **Paul** and will find the **Full Name** of every person from the list that has **Paul** in it.

Let’s see the steps.

**Steps:**

- In the beginning, select the cell where you want the result.
- Next, write the following formula in that selected cell.

`=IFERROR(INDEX($B$5:$B$14,SMALL(IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1))),"")`

- After that, press
**Enter**to get the first result. - If you are using an older version of
**Microsoft Excel**than**Excel 2019**then press**Ctrl**+**Shift**+**Enter**.

**🔎** **How Does the Formula Work?**

**SEARCH($E$5,$B$5:$B$14):**Here,**the SEARCH function**returns the location of one string inside another string.**IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE):**Now,**the IFERROR function**handles the errors the formula returns.**ROW($B$5:$B$14):**Here,**the ROW function**returns the row numbers of the selected cell range.**ROW($B$5):**This formula returns the row number of**Cell B5**.**ROW($B$5:$B$14)-ROW($B$5)+1:**Now, this formula**subtracts**the result of the second**ROW**function from the result of the first**ROW**function and then**adds 1**with it.**IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1):**The**IF**function checks if the value from**IFERROR**function is**True**or**False**. If it is**True**then it returns the value from the**ROW**functions. Otherwise, it returns**False**.**SMALL(IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1)):**Here, the**ROW**functions assist to go after the values iteratively and then**the SMALL function**returns the values one by one, depending on the order.**INDEX($B$5:$B$14,SMALL(IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1))):**Now, the**INDEX**function returns the values accordingly from**$B$5:$B$14**.**IFERROR(INDEX($B$5:$B$14,SMALL(IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1))),””):**Finally, the**IFERROR**function is set in such a way that it will return empty for any error value.

- In the following step, drag the
**Fill Handle**down to copy the formula.

- Finally, you can see that I have copied the formula to the other cells and got my desired results.

**Read More:** **VLOOKUP Partial Match Multiple Values (3 Approaches)**

## Things to Remember

- You must press
**Ctrl**+**Shift**+**Enter**for entering an array formula if you are using an older version of**Microsoft Excel**than**Excel 2019**.

## Practice Section

Here, I have provided a practice sheet for you to practice how to use **partial VLOOKUP** in Excel.

## Conclusion

That’s all for today. Here, I have listed several approaches to using **VLOOKUP** for the **partial match**. Hope you will find this helpful. Moreover, I have added the practice workbook at the beginning of the article. So, you can download it to exercise. Furthermore, feel free to comment if anything seems difficult to understand. Let me know any other methods that we might have missed here.

## Further Readings

**Excel VLOOKUP for Partial Match in Table Array (3 Examples)****How to Use VLOOKUP to Find Approximate Match for Text in Excel****Conditional Formatting for Partial Text Match in Excel (9 Examples)****How to Use INDEX and Match for Partial Match (2 Ways)****Lookup Partial Text Match in Excel (5 Methods)****How to Use IF Partial Match in Excel (4 Basic Operations)****COUNTIF Partial Match in Excel (2 or More Approaches)**