Lookup Column Header based on cell value

**kingcal** · 08-11-2009, 12:31 PM

Hi,

Hoping to tap into your collective experience to help me with this! I have a spreadsheet an excerpt of which is as follows:

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

Want I want to happen is to return the column header where there for a cell which contains "TRUE" in a blank column. If there isn't a cell with the value "TRUE" then to return "N/A". In the above example in blank column P for Row 2 would be "Doors", Row 3 would be "Security,Vehicle" and Row 4 would be "N/A".

In my actual spreadsheet I have 12 columns (L to W) which have the TRUE or FALSE values.

I know it has something to do with either HLOOKUP, INDEX and MATCH but can't seem to get it right. Any ideas?

**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.