**VLOOKUP **function is generally used for searching or finding any elements within a range or table in Excel. We can find any elements with partial matches using the **VLOOKUP **function. There are several ways to perform this type of operation. In this article, I will show different ways to find anything with the VLOOKUP function for the partial match in Excel.

**Table of Contents**hide

## Download the Practice Workbook

**4 Ways to Use VLOOKUP for Partial Match**

**1. VLOOKUP with Wildcard to get Partial/Exact Match**

Let’s assume, we have a dataset of sales information with ID, Name, Joining Date, and Sales of products.

**Step 1:** Enter the following formulas in cell **C16 **and press Enter

`=VLOOKUP("*"&C14&"*",B4:E11,1,FALSE)`

**Formula Explanation:**

- In the first argument
**“*”&C14&”*”**is the lookup value. Here we are using wildcard characters to check the lookup value. **B4:E11**this is the range where we will search the value.**1**is used as we want to extract the data from the first column.**FALSE**is used to define the exact match.- For more information about the VLOOKUP function, you can check this link

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

**2. Retrieve Data Using VLOOKUP Partial Match**

In the first part, we have extracted only one value that is Name. Now here we will find out the Name and Joining Data of the searched keyword.

**Step 1:** Enter the following formula in cell **C17 **and press **Enter**

`=VLOOKUP("*"&C14&"*",B4:E11,3,FALSE)`

**Formula Explanation**

- This formula is similar to the previous one. Just the main difference is that as we want to extract Joining Date from the 3rd column, that’s why 3 is given as the column index.

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

**3. Get Partial Match of Numerical Data with VLOOKUP**

Up till now, we have extracted only Name and Joining Date from the given dataset. Now in this section, we will find out the sales for the matched names.

**Step 1:** Enter the following formula in cell **C18 **and press **Enter **key

`=VLOOKUP("*"&C14&"*",B4:E11,4,FALSE)`

**Formula Explanation**

- This formula is the same as the previous one. Just the main difference is that as we want to extract Salary from the 3rd column, that’s why
**4**is given as the column index.

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

**4. Search Data with VLOOKUP for Partial Match and Condition**

Let’s see how we can search any information with **VLOOKUP **for partial matches and a condition. Again, we will be using the same dataset. Our task is to check if the entered keyword matches name has the highest sales or not. If the matched keyword name has the highest sales, then it will print yes, otherwise no.

**Step 1: **Enter the following formula in cell **C17 **and press **Enter**

`=IF(VLOOKUP(C16,B4:E11,4)>=E15,"Yes","No")`

**Formula Explanation**

**VLOOKUP(C16, B4:E11,4)>=E15**this is the logical condition of the**IF**function here. We are checking here if the entered name has the highest sales or not.- If the entered name’s salary is matched with our already defined highest salary, then it will return “
**Yes**”, otherwise “**No**”. - To learn more about the
**IF**function you can check this link

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

**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 matches using the formula combination of **INDEX **and **MATCH **functions.

**Excel Lookup Partial Text Match Using INDEX MATCH**

Now we will find out the full name by searching for keywords with INDEX and MATCH functions. Again the dataset will be the same.

**Step 1:** Enter the following formula in cell **C16 **and press **Enter**

`=INDEX($B$4:$B$11,MATCH("*"&C14&"*",$B$4:$B$11,0))`

**Formula Explanation**

- First, let’s see the inner function which is
**MATCH**. In the first argument**“*”&C14&”*”**this matches the data with our partial text in the Model column.**$B$4:$B$11**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,**$B$4:$B$11**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 look at this link

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

**Things to Remember**

Common Errors |
When they show |
---|---|

Match Problem | There might be a matching problem when there are conflicting data in the dataset. Results will be based on the first matched data. |

Asterisk (*) Matching Issue | If the asterisk (*) sign is not used properly, like at the front, middle, or back based on the requirements. There could be some matching issue. |

#NA in VLOOKUP/INDEX/MATCH |
If the searched value is not present in the given dataset, then all these functions will return this #NA error. |

**Conclusion**

These are some ways to find any element using partial match 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.