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.
Best Answer
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:
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.