Lookup Column Header based on cell value

**NBVC** · 08-11-2009, 12:34 PM

=Index($L$1:W$1,Match(True,$L2:$W2,0)) copied down.

**kingcal** · 08-12-2009, 06:35 AM

Excellent. Thanks very much for that - it returns an accurate result for about 90% of the rows but not for those where there are more than one "TRUE" value in the row, e.g.

K L M N
1 Doors Security Vehicle Key
2 FALSE TRUE TRUE FALSE

The formula above only returns the first column header where there is a "TRUE" value but not the second (or any other) column headers, so in the example above it would only return Security but not Vehicle. Is there anyway that all of the appropriate column headers could be seperated by a comma e.g. "Security, Vehicle"?

Many thanks for your help.

**NBVC** · 08-12-2009, 09:14 PM

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.