Excel - How to vlookup to return multiple values?

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 array-returning 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
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
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.

-

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.

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

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

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

4. Filter for "TRUE" on column D to get desired results. See below:

-

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))))`

-

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
``````
-