I’m looking to use Excel to look up and return multiple reference values for a given key. VLookup does something very similar to what I need – but only returns a single match.
I assume it’ll involve arrayreturning and handling methods, though I haven’t dealt with these before. Some Googling starts to lean on the if([lookuparray]=[value],row[lookuparray]) as part of a solution – though I can’t get it to return a single match…
For example, if I have this reference data:
Adam Red
Adam Green
Adam Blue
Bob Red
Bob Yellow
Bob Green
Carl Red
I’m trying to get the multiple return values on the right. (Comma separated, if possible)
Red Adam, Bob, Carl
Green Adam, Bob
Blue Adam
Yellow Bob
(I already have the key value on the left – no need to pull out those values)
Any help as to how to approach handling multiple values in th this context is apprecited. Thanks.
Here is Solutions:
We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.
Solution 1
Assuming you want a formula approach as stated (not using VLOOKUP, but still a formula), here is how I laid out the data:
I then used the following formula in cell C12:
=INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ROW($B$2:$B$8)MIN(ROW($B$2:$B$8))+1, ""), 1))
This is an array formula so after you copy and paste it into the cell you have to hit Ctrl+Shift+Enter
. I then just dragged it to the right and to the bottom.
If there is no value(s) remaining it gives the #NUM!
error, I gave an example for yellow in the uploaded image example.
I think a VBA/Macro approach would be a better solution if you have tons of rows.
Solution 2
Here’s the VBA solution for you. First, this is what the results look like:
And here’s the code:
Option Explicit
Function LookupCSVResults(lookupValue As Variant, lookupRange As Range, resultsRange As Range) As String
Dim s As String 'Results placeholder
Dim sTmp As String 'Cell value placeholder
Dim r As Long 'Row
Dim c As Long 'Column
Const strDelimiter = "" 'Makes InStr more robust
s = strDelimiter
For r = 1 To lookupRange.Rows.Count
For c = 1 To lookupRange.Columns.Count
If lookupRange.Cells(r, c).Value = lookupValue Then
'I know it's weird to use offset but it works even if the two ranges
'are of different sizes and it's the same way that SUMIF works
sTmp = resultsRange.Offset(r  1, c  1).Cells(1, 1).Value
If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
s = s & sTmp & strDelimiter
End If
End If
Next
Next
'Now make it look like CSV
s = Replace(s, strDelimiter, ",")
If Left(s, 1) = "," Then s = Mid(s, 2)
If Right(s, 1) = "," Then s = Left(s, Len(s)  1)
LookupCSVResults = s 'Return the function
End Function
Solution 3

Swap columns so that colors are in column A and names are in column B, and then sort on the color.

Formula in C2 (copy it down the column): =IF(A2<>A1,B2,C1 & “, ” & B2)

Formula in D2 (copy it down the column): =A2<>A3

Filter for “TRUE” on column D to get desired results. See below:
Solution 4
If you want a formula approach then it’s much simpler to get the results in separate cells, so lets assume that your first table is A2:B8 and the colours are listed again in D2:D5. Try this formula in E2
=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$D2,ROW($B$2:$B$8)ROW($B$2)+1),COLUMNS($E2:E2))),"")
confirmed with CTRL+SHIFT+ENTER
and copied across and down. When matches run out you get blanks.
Formula assumes Excel 2007 or later – if earlier version you can use COUNTIF instead of IFERROR, i.e.
=IF(COLUMNS($E2:E2)>COUNTIF($B$2:$B$8,$D2),"",INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$D2,ROW($B$2:$B$8)ROW($B$2)+1),COLUMNS($E2:E2))))
Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc bysa 2.5, cc bysa 3.0 and cc bysa 4.0