# 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.

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

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: ### 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 by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0