# Thread: Return Multiple Column headings in a single Cell Thanks:  1 Post #5144410 (1) Likes:  1 Post #5144410 (1)

1. ## Return Multiple Column headings in a single Cell

Hi,

I've a question with slight variation as it was posted in this thread.

I need to return the column headings for each matching x in a row; to a single cell instead of separate cells.

2. ## Re: Return Multiple Column headings in a single Cell

ABCDEFGHIJK
1Mark 1Mark 2Mark 3Mark 4Mark 5Mark 6Mark 7Mark 8Mark 9Mark 10
212xx45101091Mark 3,Mark 4
35x6864985xMark 2,Mark 10

Sheet1

In K2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(",",TRUE,IF(\$A2:\$J2="x",\$A\$1:\$J\$1,""))

3. ## Re: Return Multiple Column headings in a single Cell

thanks for this formula, it solved my problem!

4. ## Re: Return Multiple Column headings in a single Cell

thanks for this formula, it solved my problem!
You are welcome. Thanks for the update.

5. ## Re: Return Multiple Column headings in a single Cell

I'm using EXCEL 2013, But when i try this it is not working.
It gives below error "#NAME?"

Plz help me with another formulas.

Thanks

 Mark 1 Mark 2 Mark 3 Mark 4 Mark 5 Mark 6 Mark 7 Mark 8 Mark 9 Mark 10 Answer 1 2 X X 4 5 10 10 9 = TEXTJOIN(",",TRUE,IF(\$A2:\$J2="X",\$A\$1:\$J\$1,"")) 5 x 6 8 6 4 9 8 5 X #NAME?

A B C D E F G H I J K
1 Mark 1 Mark 2 Mark 3 Mark 4 Mark 5 Mark 6 Mark 7 Mark 8 Mark 9 Mark 10
2 1 2 x x 4 5 10 10 9 1 Mark 3,Mark 4
3 5 x 6 8 6 4 9 8 5 x Mark 2,Mark 10
Sheet1

In K2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(",",TRUE,IF(\$A2:\$J2="x",\$A\$1:\$J\$1,""))

6. ## Re: Return Multiple Column headings in a single Cell

Originally Posted by NITIN MANE

I'm using EXCEL 2013, But when i try this it is not working.
It gives below error "#NAME ?"

Plz help me with another formulas.

Thanks

[…]
Excel 2013 does not have the TEXTJPOIN function, hence the #NAME ? error. Try the following instead...

Add the following VBA code to your workbook as a module, using Alt+F11...

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

Then invoke...

Control+shift+enter, not just enter:

=REPLACE(ACONCAT(IF(\$A2:\$J2="x",","&\$A\$1:\$J\$1,"")),1,1,"")