Vlookup to Return Multiple Values

Excel Vlookup to Return Multiple Values

One of the key functionality of the VLOOKUP function is that it will work for unique values and if there are any duplicate values then whatever first found value will be returned for all the other lookup values as well. This is one of the key things we need to keep in mind while applying a VLOOKUP formula. When the lookup value appears multiple times and if it has multiple values then we need to include different strategies, in this article we will show you how to return multiple values using the VLOOKUP function.

How to Return Multiple Values using Vlookup Function?

As we have told above VLOOKUP works for unique values and for duplicate values it will return the first found value.

For example, look at the below data.

We have “Table 1” & “Table 2”, in “Table 1” we have fruits and their prices in different cities and for “Table 2” we need to arrive cost price from “Table 1” using VLOOKUP function. Apply the VLOOKUP function first.

• Look we have got the same price for all the cities. For example in “Table 1” for “Apple” in the city “Bangalore”, we have 108 since this is the first value found in the table for “Apple” it has returned the same for all the cities.
• Similarly for “Grapes” to the first value is 79 and the same has been returned for all the cities and in the case of “Orange” as well it returned 56 for all the cities.

So, in these cases, we need to create a helper column to create a unique lookup value list. Each fruit has different prices for each city, so a combination of fruit name and city can create a unique list, insert a helper column and combine fruit name and city name.

So each fruit name combined with the city by including backward slash (/) as the separator between fruit name and city name.

Now come back to “Table 2” and open the VLOOKUP function.

Now we need to include the same strategy of helper column here to choose lookup value, first choose fruit name.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Then combine backward slash before combining with the city name.

Now combine the city name.

Now the lookup value is similar to the helper column, now choose the table array starting from the helper column.

Now mention the column number as 4 and range lookup as FALSE or 0.

There you go we have a new cost price list with accurate numbers, say thanks to helper column or the combination of Fruit Name & City.

Use Alternative Methods for Multiple Values

We have seen how the helper column can be helpful to fetch the multiple values using the VLOOKUP formula. But imagine the situation below.

In this, we don’t have any city name to create a concatenation column, so we may need to employ different strategies below is the complex formula that we can use to get the multiple values of duplicate unique values.

This formula looks lengthy, isn’t it? However, we have another alternative method i.e. combine the fruit name with their count on the list.

Apply the below COUNTIF function to create a helper column.

The above function will give us the count of each fruit combined with the fruit name itself. For an example look at the row number 4 in this we have a count of “Apple” 2 times and so count says 2 and combined with fruit name gives us “2Apple”. So this will create a unique list of fruits.

Now create a lookup table like the below one.

Now open VLOOKUP function in lookup table i.e. in H3 cell.

In the helper column first value combined counts, so here to select the numerical value and then combine with fruit name.

Now select the table and enter the column index number to get the result.

Things to Remember

• VLOOKUP returns the same value for the lookup values if the lookup value has duplicate names.
• To fetch multiple values of the same lookup value we need to create helper columns by using any of the above 3 methods.

Recommended Articles