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

vlookup for partial match

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.

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.

vlookup for partial match

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

vlookup for partial match

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

vlookup for partial match

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.

vlookup for partial match

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.

vlookup for partial match

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

vlookup for partial match

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.

vlookup for partial match

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.

vlookup for partial match

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

vlookup for partial match

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.

vlookup for partial match

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

vlookup for partial match

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

vlookup for partial match

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

Formula using INDEX and MATCH functions

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

vlookup for partial match

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.

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