Excel Vlookup to Return Multiple Values

One key functionality of the VLOOKUP function is that it will work for unique values. If there are duplicate values, then whatever is the first found value will be returned for all the other lookup values. One of the key things we need to remember while applying a VLOOKUP formula. When the lookup_value appears multiple times, we need to include different strategies if it has multiple values. This article 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. For duplicate values, it will return the first found value.

Look at the below data.

We have “Table 1” and “Table 2.” In “Table 1,” we have fruits and their prices in different cities. For “Table 2,” we need to determine the “Cost Price” from “Table 1” using the VLOOKUP function. Therefore, 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.

  • We have 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,” the first value is 79. The same has been returned for all the cities. In the case of “Orange” also, it provides 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 names and cities can create a unique list. Insert a helper column, and combine fruits and cities name.

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

Now, return to “Table 2” and open the VLOOKUP function.

Now, we need to include the same strategy of the helper column here to choose the lookup_value. But, first, choose the 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 column number 4 and range_lookup as FALSE or 0.

We have a new cost price list with accurate numbers, thanks to the helper column or the combination of fruit and city names.

Use Alternative Methods for Multiple Values

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

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

Note: The above formula is an array formulaArray FormulaArray formulas are extremely helpful and powerful formulas that are used in Excel to execute some of the most complex calculations. There are two types of array formulas: one that returns a single result and the other that returns multiple results.read more, so you must close with Ctrl + Shift + Enter.

This formula looks lengthy. However, we have another alternative method: 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 its name. For example, look at row number 4. In this, we have a count of “Apple” 2 times, so the 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 in the H3 cell.

The first value combined counts in the helper column, so select the numerical value and connect it with the fruit name.

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 must create helper columns using the above three methods.

This article is a guide to VLOOKUP to Return Multiple Values. Here, we discuss how to return multiple values using the VLOOKUP function, alternative methods for multiple values, and a downloadable Excel template. You may also look at these useful functions in Excel:-

  • Use VLOOKUP with Power BIApply VLOOKUP for TextApply VLOOKUP with MatchVLOOKUP Excel Tutorial