VLOOKUP Partial Match Multiple Values (3 Approaches)

Circumstances may demand a partial match with multiple values. Our Agenda for today is to assist you in performing a partial match using VLOOKUP with multiple values. For this session, we are using Excel 2019, feel free to use yours.

First things first, let’s get to know about the workbook which is the base of our examples.

Dataset - VLOOKUP Partial Match Multiple Values

Here we have a table of several salespersons along with their sales item and quantity. Using this data we will perform the partial match with multiple values.

Note that this is a simple table with dummy data. In a real-life scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Multiple Values Partial Match using VLOOKUP

Here in our examples to keep things straightforward, we will use two values while finding the matches.

Criteria - VLOOKUP Partial Match Multiple Values

We will provide the name and item value and in return will get the quantity. Since our formula will be based on VLOOKUP, you can check the function from this article; VLOOKUP.

1. Partial Match Multiple Values Using Helper Column

In this section, we will show how to perform a partial match with multiple values using a helper column.

Helper column - VLOOKUP Partial Match Multiple Values

The helper column will be filled by concatenating the original column of the lookup values. The concatenation can be done using the ampersand (&) sign.

=C4&D4

Concatenate Helper column - VLOOKUP Partial Match Multiple Values

After filling up the helper column let’s add the two criteria values.

Criteria value - VLOOKUP Partial Match Multiple Values

Here the provided values are Adam and Banana. You can see the initial table doesn’t have the item Banana, so we need to build the formula in such a way that it will return value in this kind of case.

The formula will be the following one

=VLOOKUP(H3&"*"&H4&"*",$B$4:$E$15,4,0)

Here H3 and H4 are the values to look at.

VLOOKUP formula - VLOOKUP Partial Match Multiple Values

And the asterisk (*) sign is the wildcard. When there is an asterisk sign, it denotes that any number of characters (including 0) can take the place.

Here we have used two asterisks, one within the two values and another one at the end of the values. Hope you have understood that we have concatenated these together before starting searching within the lookup_array.

Our desired result is within the 4th column, so used 4 in the column_num field.

VLOOKUP formula result - VLOOKUP Partial Match Multiple Values

It provided the number of bananas sold by Adam. (Though we had set Banana)

Let’s change the values and see the updated result.

Change of values - VLOOKUP Partial Match Multiple Values

Here we have set Nic and App at the Name and Item values. You can see no values as such listed in the original table.

But it gave the values from Nicolas who sales Apples. As the provided values partially matched with these values.

2. Partial Match Multiple Values using Helper Function

In this section, we will show how to perform the task without any helper column. Rather this time we will use a helper function called CHOOSE.

Helper function - VLOOKUP Partial Match Multiple Values

The CHOOSE function returns a value from a list using a given position or index. And the syntax of this function is

CHOOSE(index_num, value1, [value2], ...)

index_num: A number between 1 to 254, specifies the value argument.

value1: A value from which to choose.

value2: Second value to choose. This is an optional field.

To know more about the function please visit the Microsoft Support site.

Now let’s write the formula to pass two lookup values and the formula will be

=VLOOKUP(G3&"*"&G4&"*",CHOOSE({1,2},$B$4:$B$15&$C$4:$C$15,$D$4:$D15),2,0)

Here the use of the two values and the wildcard is similar to the formula of the earlier section.

VLOOKUP - CHOOSE - VLOOKUP Partial Match Multiple Values

The CHOOSE portion of this formula works as a virtual helper table. We have used 1 and 2 (within the curly braces) as the index number.

Then we have concatenated the Name and Sales Item column together, this will be the first column of our virtual table.

Here we have inserted the Quantity column in the value2 field and this will be the second column of the virtual table.

This table becomes the lookup_array for the VLOOKUP function. Since our desired result would be found at the second column from the virtual table we have used 2 as the column_number.

VLOOKUP - CHOOSE result - VLOOKUP Partial Match Multiple Values

Here, our provided values are Jord and Black, and the formula derived value from Jordan and Blackberries.

Change the values to see the updated result.

Change of values - VLOOKUP Partial Match Multiple Values

The values we have set are Poter and Apple (no such values in the list), the formula provided the value for Poterfield where it found the partial match.

3. Determine Partial Match using INDEX – AGGREGATE

We can define the values by partial match operation. In this section, we will see how to match the category performing a partial match.

Dataset 2 - VLOOKUP Partial Match Multiple Values

Here we have some products and categories listed separately. We aim to compare the product name with the category name and name the category when it finds a match.

You can see hardly a product name can be exactly the same as the category, so a partial match is the key.

This time we will use the INDEX – AGGREGATE combination.

The INDEX function returns the value at a given location in an array or range. For further information, visit this article; INDEX.

The AGGREGATE function returns an aggregate calculation like AVERAGE, COUNT, MAX, etc.

The syntax for the AGGREGATE function is as follows

AGGREGATE(function_number,behavior_options, range)

function_number: This number specifies which calculation should be made.

behavior_options: Set this using number. This number denotes how the function will behave.

range: Range you want to aggregate.

The AGGREGATE function does several tasks so numbers of functions are predefined within it. We are listing few frequently used function numbers

Function Function_number
AVERAGE 1
COUNT 2
COUNTA 3
MAX 4
MIN 5
PRODUCT 6
SUM 9
LARGE 14
SMALL 15

To know more about the function, visit the Microsoft Support site.

Now let’s see the formula,

=INDEX(E$4:E$7,AGGREGATE(15,6,MATCH("*"&$E$4:$E$7&"*",$B4,0)*(ROW($E$4:$E$7)-ROW(E$4)+1),1))

E4:E7 is the array within which we need to find.  Within AGGREGATE we have set 15 as function_number and 6 as behavior_option that stands for ignoring error values.

And the ROW functions provide the row number iteratively.

INDEX - AGGREGATE formula - VLOOKUP Partial Match Multiple Values

The formula provided the category name perfectly after comparing the product name and category list.

Write the formula for the rest of the products.

INDEX - AGGREGATE - VLOOKUP Partial Match Multiple Values

Conclusion

That’s all for today. We have tried listing several ways to perform the partial match using VLOOKUP with multiple values. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods which we might have missed here.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo