Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

Assuming you want a formula approach as stated (not using VLOOKUP, but still a formula), here is how I laid out the data:

Data Layout

I then used the following formula in cell C12:

=INDEX($C$2:$C$8, SMALL(IF($B$12=$B$2:$B$8, ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""), COLUMN(A1)))

I then just dragged it to the right. This is an array formula so after you copy and paste it into the cell you have to hit Ctrl+Shift+Enter.

Then all you need to do is change the value immediately after IF( for each row (B12, B13, B14, etc.)

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.

share|improve this answer
add comment
  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:

enter image description here

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.