Lookup Column Header based on cell value

The easiest way is with the help of an addin or udf function....

If you want an addin, try this free addin that include many more useful functions..it's called Morefunc.xll and can be downloaded from here:


Then apply this formula:

=SUBSTITUTE(TRIM(MCONCAT(IF($L2:$W2=TRUE,$L$1:W$11,"")," "))," ",", ")

confirmed with CTRL+SHIFT+ENTER, not just ENTER and copied down.

Or you can apply this popular UDF:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

and then apply formula:

=SUBSTITUTE(TRIM(aconcatT(IF($L2:$W2=TRUE,$L$1:W$11,"")," "))," ",", ")

again, confirmed with CTRL+SHIFT+ENTER and copied down.