Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VLOOKUP for Partial Match in Excel (4 Suitable Ways) 

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 you 4 suitable ways to find anything with the VLOOKUP function for the partial match in Excel.


Download the Practice Workbook

You can download our practice workbook from here for free!


4 Suitable Ways to Use VLOOKUP for Partial Match

1. VLOOKUP with Wildcard to Get Partial/Exact Match from Single Column

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

Now, we want to look up names from this dataset with partial inputs. You can use the  VLOOKUP function with wildcards in this regard. Follow the steps below to accomplish this.

📌 Steps:

  • First and foremost, enter the following formula in cell C15 and press the Enter key.
=VLOOKUP("*"&C14&"*",B5:E12,1,FALSE)

VLOOKUP Formula to Get Partial Match

🔎 Formula Breakdown:

  • In the first argument “*”&C14&”*” is the lookup value. Here we are using wildcard characters to check the lookup value.
  • B5:E12 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.

  • At this time, enter any keyword in cell C14 and press the Enter key.

Got Partial Match through VLOOKUP Function

Thus, you would be able to find a partial match for any number of characters and within any part of the text from the lookup range by using the VLOOKUP function with wildcards.

Read More: VLOOKUP with Wildcard in Excel (3 Methods)


2. Retrieve Partial Matched Data from a Range

In the first part, we have extracted only one value which is Name. Now here we will retrieve the Name and Joining Data from the searched keyword with a partial match. Follow the steps below to do this.

Sample Dataset to Retrieve Multiple Values for Partial Match

📌 Steps:

  • First, click on cell C16 and insert the following formula.
=VLOOKUP("*"&C14&"*",B5:E12,3,FALSE)

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

  • Subsequently, press the Enter key.

VLOOKUP Function to Retrieve Multiple Values for Partial Match

  • Following, enter any keyword in the search box on cell C14 and press Enter.

Retrieved Multiple Values for Partial Match

Thus, you will be able to retrieve multiple-column data with the VLOOKUP function by partial match.

Read More: VLOOKUP and Return All Matches in Excel (7 Ways)


3. Get a 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. Follow the steps below to achieve this target.

📌 Steps:

  • At the very beginning, click on cell C17 and insert the following formula.
=VLOOKUP("*"&C14&"*",B5:E12,4,FALSE)
  • Following, press the Enter key.

VLOOKUP Formula to Extract Numerical Values with Partial Match

🔎 Formula Breakdown:

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

  • At this time, enter any keyword in the search box on cell C14 and press the Enter key.

Extracted Numerical Values for Partial Match

As a result, you will be able to lookup for multiple values with partial matches including numerical values.

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


Similar Readings


4. Search Data with VLOOKUP for Partial Match and Conditions

Now, Let’s see how we can search for any information with VLOOKUP for partial matches and a condition. We will be using the same dataset similarly. Our task is to check if the entered keyword matching name has the highest sales or not. In this regard, you can use the MAX function to get the highest sales. If the matched keyword name has the highest sales, then it will print yes, otherwise no. Follow the steps below to do this.

  • Initially, enter the following formula in cell C16 and press the Enter key.
=IF(VLOOKUP(C15,B5:E12,4)>=E15,"Yes","No")

VLOOKUP Formula to Apply Condition with VLOOKUP for Partial Match

🔎 Formula Breakdown:

  • VLOOKUP(C15, B5:E12,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.

  • Afterward, enter any keyword in the search box on cell C14 and press the Enter key.

Got Value with Condition for Partial Match

Consequently, you will find the conditional answer in cell C16 with a partial match for the VLOOKUP function.

Read More: How to Return the Highest Value Using VLOOKUP in Excel


Excel VLOOKUP Not Working for Partial Match: What Are the Reasons?

The VLOOKUP function with partial match is an intricate task sometimes. So, you might find errors or fail to get the desired result for several reasons. The main reasons for the VLOOKUP partial match not working are given as follows.

  • If the wild card character is in the wrong placement.
  • If the column number is inappropriate inside the VLOOKUP function.
  • If the search value does not exist in the source data’s lookup region, you will get the #N/A errors.
  • If there is extra space or unnecessary characters inside the search value or source range values.
  • If there are multiple matches for a single lookup value, then the first lookup value will be shown in the result.

INDEX-MATCH: An Alternative to VLOOKUP for Partial Match in Excel

In addition to these, 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.

At this time, we will find out the full name by searching for keywords with INDEX and MATCH functions. Go through the steps below to achieve this target.

📌 Steps:

  • First, enter the following formula in cell C15 and press the Enter key.
=INDEX($B$5:$B$12,MATCH("*"&C14&"*",$B$5:$B$12,0))

INDEX-MATCH Formula to Get Values for Partial Match

🔎 Formula Breakdown:

  • 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$5:$B$12 this is the Model column range. 0 is used to define the exact match.
  • Afterward, in the INDEX function, $B$5:$B$12 is the range where we will find the index. The return result of the MATCH data will be considered as a row number.

  • Afterward, enter any keyword in the search box on cell C14 and press the Enter key.

Thus, you will get your desired result in cell C15 using the INDEX-MATCH combination.

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


Conclusion

So, in this article, I have shown you 4 suitable ways to use the VLOOKUP function for a partial match in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to learn more things about Excel! Have a nice day! Thank you!


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo