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

Get FREE Advanced Excel Exercises with Solutions!

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.


1. Using VLOOKUP with Wildcard for Partial/Exact Match from a 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.


2. Achieving Partial Matched Data from a Range with VLOOKUP Function

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.


3. Getting Partial Match of Numerical Data with Excel VLOOKUP Function

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: Use VLOOKUP to Find Multiple Values with Partial Match in Excel


4. Searching 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 Perform VLOOKUP with Wildcard 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.

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


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.


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.

Have a nice day! Thank you!


VLOOKUP Partial Match: Knowledge Hub


<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo