Formula Troubles

[TerenceTitus]

Hi,

I am having troubles combining different cells into 1. Here is what I have so far.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Lesson Study[/TD]
[TD]Learning Study[/TD]
[TD]Action Research[/TD]
[TD]Video-based Research[/TD]
[TD]Learning Circle[/TD]
[TD]Combined[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So far I have used index match and vlookup for combined column, but it could not work. An example of how it would look like for the last row - "Lesson Study & Action Research & Video-based Research & Learning Circle".

I would need to insert the "&" after each statement, but I got it mixed up after reaching the third statement and it didn't work.

Appreciate your help here.

Regards
Terence

 

[StephenCrump]

Assuming A1 is "Lesson Study"

then F2: =TRIM(MID(IF(A2," & "&A$1,"")&IF(B2," & "&B$1,"")&IF(C2," & "&C$1,"")&IF(D2," & "&D$1,"")&IF(E2," & "&E$1,""),3,999))