Use Index/Match and/or VLOOKUP for multiple columns then concatenate

Question

I'd like to use column D as Index or lookup value. Then I want to concatenate the values from column B and C into Column E.

I can use =VLOOKUP(D2,A2:C6,2,FALSE) or =INDEX($B$2:$B$6,MATCH("Person 1",$A$2:$A$6,0)) but I don't know how to use it multiple times in the same column.Is there a way I combine those to search multiple times in the same column?

I'd be open to using vba if that would be a better option, just still not sure about the multiple times per column.

Answer 1

I couldn't figure out magic one in done formula. Richard Tompsett is probably correct with VBA solution. I'd recommend the following series of steps if VBA is out of the question.

(1) Sort by column A. This will group Thing 1 and Thing 2 together into discrete ranges per person.

(2) In cell F2, type =Transpose(B2:C3) and hit F9. Should convert to look like ={"A","D";1,4}, then delete curly brackets and equal sign. This is the range for Person 1 (manually).

(3) In cell E2, enter =SUBSTITUTE(SUBSTITUTE(F2,"""",""),";",","). Should now appear as ' A,D,1,4 '

Repeat for each Person