Return multiple values from a range

Question

To return a value corresponding to another cell from a range if it matches with a cell, I found Chuff's solution helpful (in Excel - match data from one range to another and get the value from the cell to the right of the matched data ):

=iferror(vlookup(b31,$f$3:$g$12,2,0),"")

However, if there are more than one cells within the column F which match with b1, the formula returns the value of only one cell from the column G.

Could it be modified so as to attract the value of more than one cell?

Thank you!

In the case of multiple matches, how do you want the values from the corresponding cells of column G to be presented? eg in adjacent cells, or coerced into a single using an operation such as SUM() (if they are all numeric) or concatenation (otherwise). — DMM

Kresimir L. Kresimir L. · Accepted Answer · 2017-09-17T09:13:37

To return multiple corresponding Vlookup values you should use this formula: =IFERROR(INDEX($B$2:$B$9,SMALL(IF($E1=$A$2:$A$9,ROW($A$2:$A$9)-ROW($A$2)+1),COLUMN(A1))),"") Because it it an array formula, please enter it using combination of CTRL+SHIFT+ENTER . For example, if you have you lookup range in A:B column, and lookup values in D column, then please enter formula above to F1 cell, then drag it to the right and to the bottom. You should now see all instances of Vlookup next to the lookup value in D column.

If you have only values which you want to sum in case they correspond to your value in cell B31, then simply use SUMIF formula like this =SUMIF($F$3:$F$10,$B31,$G$3:$G$10) entered in cell C31.

Return multiple values from a range

1 Answers