Shakil Ahmed
There may arise a situation where you need to perform a partial matching within VLOOKUP. To assist you, today I am going to show you how to operate partial matching using the VLOOKUP function in Excel. For this session, I am using Office 365, feel free to use any version.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
Introduction to VLOOKUP Function
Summary:
The VLOOKUP function looks for a given value in the leftmost column of a given data range and then returns a value in the same row from the specified column.
Syntax:
The syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])Arguments:
| ARGUMENT | REQUIRED/OPTIONAL | EXPLANATION |
| lookup_value | Required | The value that it looks for is in the leftmost column of the given table. Can be a single value or an array of values. |
| table_array | Required | The table in which it looks for the lookup_value in the leftmost column. |
| col_index_num | Required | The number of the column in the table from which a value is to be returned. |
| [range_lookup] | Optional | Tells whether an exact or partial match of the lookup_value is required. 0 for an exact match, 1 for a partial match. The default is 1 (partial match). |
Return Value:
Returns the value of the same row from the specified column of the given table, where the value in the leftmost column matches the lookup_value.
Available in:
Excel 365 | Excel 2021 | Excel 2021 for Mac | Excel 2019 | Excel 2019 for Mac | Excel 2016 | Excel 2016 for Mac | Excel 2013, 2010, 2007 | Excel for Mac 2011 | Excel Starter 2010
5 Suitable Examples of Using Partial VLOOKUP in Excel
First things first, let’s get to know about the dataset which is the base of our examples. Here I have a table that contains 3 columns, Sales Person, State, and Sales. Using this dataset, I will operate the partial match in Excel VLOOKUP.
Note that this is a basic table to keep things straightforward. In a real-life scenario, you may encounter a much larger and more complex dataset.
For a partial match, you will need to use the asterisk sign (*) as a wildcard. Any number of characters can take place in place of an asterisk. You will understand more when I will show examples.
1. Match Value from Beginning Using Partial VLOOKUP in Excel
You know that the VLOOKUP function searches for a value within a given array and produces the result. So, you can provide the value within it to find the match and return the answer.
Here, I am going to search for a name and when the match is found, the formula will return the corresponding State. In this section, I will show you how to match a value that is at the beginning of a string.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want the corresponding State. Here, I selected Cell C14.
- Secondly, in Cell C14 write the following formula.
=VLOOKUP(C13&"*",$B$5:$D$11,2,0)
- Thirdly, press Enter to get the result.
🔎 How Does the Formula Work?
- In the VLOOKUP function, I selected C13&”*” as lookup_value. C13 contains the searching value, Rod. And, the asterisk sign
(*)denotes that any number of characters can take place. The ampersand(&)sign joins these two. So, the lookup_value stands as such, the value can be anything that starts with Rod. - Here, the table_array is $B$5:$D$11 and I have used 2 as col_index_no since I need the value from the 2nd column.
- 0 is defining that I want to get an exact match.
Read More: [Fixed!] Excel VLOOKUP Partial Match Not Working
2. Apply Partial VLOOKUP to Match Value at End of String
In the earlier section, you have seen how to match the value from the beginning of the string. In this section, I will show you how you can use the VLOOKUP function for a partial match at the end of the string. For this example, I will find the corresponding Sales for a specific Last Name.
Let me show you how to do it.
Steps:
- In the beginning, select the cell where you want the Sales. Here, I selected Cell C14.
- Next, in Cell C14 write the following formula.
=VLOOKUP("*"&C13,$B$5:$D$11,3,0)
- In the following step, press Enter to get the corresponding Sales.
🔎 How Does the Formula Work?
- In the VLOOKUP function, I selected “*”&C13 as lookup_value, $B$5:$D$11 as table_array, 3 as col_index_num, and 0 as range_lookup.
- Here, for the lookup_value I used the asterisk sign
(*)at first so the string can start with any character. And then, the ampersand (&) will join it with the value in Cell C13. So, the lookup_value can be anything that ends with the value in Cell C13. - The table_array is $B$5:$D$11 and I have used 3 as col_index_no since I need the value from the 3rd column.
- 0 is defining that I want to get an exact match.
Read More: How to Perform Partial Match String in Excel (5 Methods)
3. Employ Partial VLOOKUP Irrespective of Position in Excel
If you need to match the value that can be either at the beginning or in the middle or at the end of the string, then this example will be a helpful one. Let’s see how you can do that.
Steps:
- Firstly, select the cell where you want to find the match.
- After that, write the following formula in that selected cell.
=VLOOKUP("*"&C13&"*",$B$5:$D$11,3,0)
- After that, press Enter to get the result.
🔎 How Does the Formula Work?
- In the VLOOKUP function, I selected “*”&C13&”*” as lookup_value, $B$5:$D$11 as table_array, 3 as col_index_num, and 0 as range_lookup.
- Here, for the lookup_value I used the asterisk sign
(*)both at first and at the end. So, the lookup_value can be anything that contains the value in Cell C13. - The table_array is $B$5:$D$11 and I have used 3 as col_index_no since I need the value from the 3rd column.
- 0 is defining that I want to get an exact match.
Read More: VLOOKUP Partial Text from a Single Cell in Excel
4. Get Multiple Results Using Partial VLOOKUP in Excel
In this example, I will show you how you can use the Excel VLOOKUP function for a partial match and get multiple results. Here, I will search for a value and then return the corresponding information from the table.
Let’s see the steps.
Steps:
- To begin with, select the cell where you want the first match. Here, I selected Cell B16.
- Then, in Cell B16 write the following formula.
=VLOOKUP("*"&C13&"*",$B$5:$D$11,{1,2,3},0)
- Afterward, press Enter to get the result.
- If you are using an older version of Microsoft Excel than Excel 2019, press Ctrl + Shift + Enter.
🔎 How Does the Formula Work?
- In the VLOOKUP function, I selected “*”&C13&”*” as lookup_value, $B$5:$D$11 as table_array, {1,2,3} as col_index_num, and 0 as range_lookup.
- Here, for the lookup_value I used the asterisk sign
(*)both at first and at the end. So, the lookup_value can be anything that contains the value in Cell C13. - The table_array is $B$5:$D$11 and I have used {1,2,3} as col_index_no since I need the value from all the 3 columns.
- 0 is defining that I want to get an exact match.
5. Employ IF and VLOOKUP Functions for Partial Match in Excel
Suppose, you want to know if a Sales Person has the highest sales or not then you can use the VLOOKUP function for that. So, I will show you how you can use the VLOOKUP function for a partial match in Excel to find out If a Sales Person has the highest Sales or not.
Let me show you the steps.
Steps:
- Firstly, select the cell where you want the result.
- Secondly, write the following formula in that selected cell.
=IF(VLOOKUP("*"&C13&"*",$B$5:$D$11,3,0)=MAX(D5:D11),"Yes","No")
- Thirdly, press Enter to get the result.
🔎 How Does the Formula Work?
- MAX(D5:D11): Here, in the MAX function, I selected cell range D5:D11 as numbers. The formula will return the largest value in the given range which is the highest Sales.
- VLOOKUP(“*”&C13&”*”,$B$5:$D$11,3,0): Now, in the VLOOKUP function, I selected “*”&C13&”*” as lookup_value, $B$5:$D$11 as table_array, 3 as col_index_num, and 0 as range_lookup. The formula will return the partial match for C13 from column 3 which is the Sales for a specific Sales Person.
- IF(VLOOKUP(“*”&C13&”*”,$B$5:$D$11,3,0)=MAX(D5:D11),”Yes”,”No”): Here, the IF function will check if the result from the VLOOKUP function is equal to the value I got from the MAX function. If the logical_test is True then the formula will return Yes. Otherwise, It will return No.
Similar Readings
- How to Vlookup Partial Match for First 5 Characters in Excel
- Excel Partial Match Two Columns (4 Simple Approaches)
- Excel SUMIF with Partial Match (3 Ways)
An Alternative to Partial VLOOKUP in Excel
You can use the combination of INDEX and MATCH functions. This can be a useful alternative to the VLOOKUP for a partial match. The MATCH function locates the position of a lookup value and the INDEX function returns the value from the given location. So, I will use the same example to get the same result using a different formula. Let’s see how you can do that.
Steps:
- First, select the cell where you want to find the match. Here, I selected Cell C14.
- Secondly, in Cell C14 write the following formula.
=INDEX($C$5:$C$11,MATCH("*"&C13&"*",$B$5:$B$11,0))
- After that, press Enter to get the result.
🔎 How Does the Formula Work?
- MATCH(“*”&C13&”*”,$B$5:$B$11,0): Within the MATCH function, I have compared the values and returned the position when it was found.
- INDEX($C$5:$C$11,MATCH(“*”&C13&”*”,$B$5:$B$11,0)): Now, the INDEX function returns the value according to the location number. Since I want the output from the State column, I have provided $C$5:$C$11 as the array reference within INDEX.
Read More: How to VLOOKUP Partial Text in Excel (With Alternatives)
Advanced Use of Partial VLOOKUP in Excel
You may find several advanced scenarios to use the VLOOKUP function. Here I am mentioning an advanced operation, where I will find all the values corresponding to the matched value.
For example, I have set the criteria value as Paul and will find the Full Name of every person from the list that has Paul in it.
Let’s see the steps.
Steps:
- In the beginning, select the cell where you want the result.
- Next, write the following formula in that selected cell.
=IFERROR(INDEX($B$5:$B$14,SMALL(IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1))),"")
- After that, press Enter to get the first result.
- If you are using an older version of Microsoft Excel than Excel 2019 then press Ctrl + Shift + Enter.
🔎 How Does the Formula Work?
- SEARCH($E$5,$B$5:$B$14): Here, the SEARCH function returns the location of one string inside another string.
- IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE): Now, the IFERROR function handles the errors the formula returns.
- ROW($B$5:$B$14): Here, the ROW function returns the row numbers of the selected cell range.
- ROW($B$5): This formula returns the row number of Cell B5.
- ROW($B$5:$B$14)-ROW($B$5)+1: Now, this formula subtracts the result of the second ROW function from the result of the first ROW function and then adds 1 with it.
- IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1): The IF function checks if the value from IFERROR function is True or False. If it is True then it returns the value from the ROW functions. Otherwise, it returns False.
- SMALL(IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1)): Here, the ROW functions assist to go after the values iteratively and then the SMALL function returns the values one by one, depending on the order.
- INDEX($B$5:$B$14,SMALL(IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1))): Now, the INDEX function returns the values accordingly from $B$5:$B$14.
- IFERROR(INDEX($B$5:$B$14,SMALL(IF(IFERROR(SEARCH($E$5,$B$5:$B$14)>0,FALSE),ROW($B$5:$B$14)-ROW($B$5)+1),ROW(1:1))),””): Finally, the IFERROR function is set in such a way that it will return empty for any error value.
- In the following step, drag the Fill Handle down to copy the formula.
- Finally, you can see that I have copied the formula to the other cells and got my desired results.
Read More: VLOOKUP Partial Match Multiple Values (3 Approaches)
Things to Remember
- You must press Ctrl + Shift + Enter for entering an array formula if you are using an older version of Microsoft Excel than Excel 2019.
Practice Section
Here, I have provided a practice sheet for you to practice how to use partial VLOOKUP in Excel.
Conclusion
That’s all for today. Here, I have listed several approaches to using VLOOKUP for the partial match. Hope you will find this helpful. Moreover, I have added the practice workbook at the beginning of the article. So, you can download it to exercise. Furthermore, feel free to comment if anything seems difficult to understand. Let me know any other methods that we might have missed here.
Further Readings
- Excel VLOOKUP for Partial Match in Table Array (3 Examples)
- How to Use VLOOKUP to Find Approximate Match for Text in Excel
- Conditional Formatting for Partial Text Match in Excel (9 Examples)
- How to Use INDEX and Match for Partial Match (2 Ways)
- Lookup Partial Text Match in Excel (5 Methods)
- How to Use IF Partial Match in Excel (4 Basic Operations)
- COUNTIF Partial Match in Excel (2 or More Approaches)
