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 at cost price from “Table 1” using the VLOOKUP function. Apply the VLOOKUP functionApply The VLOOKUP FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more 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 the helper column here to choose lookup value, first choose fruit name.

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 the 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 in fetching 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 functionCOUNTIF FunctionThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more 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 the fruit name gives us “2Apple”. So this will create a unique list of fruits.

Now create a lookup tableLookup TableLookup tables are simply named tables that are used in combination with the VLOOKUP function to find any data in a large data set. We can select the table and name it, and then type the table's name instead of the reference to look up the value.read more like the below one.

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

In the helper column, the first value combined counts, so here to select the numerical value and then combine with the 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

This has been a guide to VLOOKUP to Return Multiple Values. Here we discuss how to return multiple values using the VLOOKUP function and to use alternative methods for multiple values and downloadable excel template. You may also look at these useful functions in excel –

• Use VLOOKUP with Power BI
• Apply VLOOKUP for Text
• Apply VLOOKUP with Match
• VLOOKUP Excel Tutorial