1

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.

enter image description here

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.

mcadamsjustin
  • 297
  • 4
  • 11
  • 23
  • 1
    Unfortunately you can't concatenate an array object, this might be a task for VBA... I'll leave this for now and see what the community has to offer – Glitch_Doctor Jan 09 '18 at 16:24
  • That's what I assumed, but I was hoping I was missing something, thanks for the bad news of reality – mcadamsjustin Jan 09 '18 at 16:29

1 Answers1

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

enter image description here

Nicho247
  • 202
  • 1
  • 11