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)
🔎 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.
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.
📌 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.
- Following, enter any keyword in the search box on cell C14 and press Enter.
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.
🔎 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.
As a result, you will be able to lookup for multiple values with partial matches including numerical values.
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")
🔎 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.
Consequently, you will find the conditional answer in cell C16 with a partial match for the VLOOKUP function.
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))
🔎 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!
