How best to pull the name of column header into a cell if certain conditions are met across multiple-CodePudding

Basically, I have an excel sheet similar to the below. I want to use the YES cells to create an equation for the source column which indicates the source of the yeses.

Header 1	Header 2	Header 3	Source
YES	NO	NO	Header 1
NO	YES	NO	Header 2
NO	YES	YES	Header 2 and Header 3

CodePudding user response：

For 2019, use some alternative approaches.

• Formula used in cell D2

=TEXTJOIN(" and ",,REPT($A$1:$C$1,A2:C2="YES"))

• Formula used in cell D2

=SUBSTITUTE(TEXTJOIN(", ",,REPT($A$1:$C$1,A2:C2="YES")),", ",
" and ",IF(COUNTIF(A2:C2,"YES")-1=0,"1",COUNTIF(A2:C2,"YES")-1))

• Formula used in cell F2

=TEXTJOIN(" and ",,IFERROR(INDEX($A$1:$C$1,AGGREGATE(15,6,(
COLUMN($A2:$C2)-COLUMN($A2) 1)/($A2:$C2="YES"),COLUMN(A1:Z1))),""))

Need to press CTRL SHIFT ENTER

Edit, replacing the last comma with and, which will be the right thing to use,

• Formula used in cell F2

=SUBSTITUTE(TEXTJOIN(", ",,IFERROR(
INDEX($A$1:$C$1,AGGREGATE(15,6,(
COLUMN($A2:$C2)-COLUMN($A2) 1)/($A2:$C2="YES"),COLUMN(A1:Z1))),"")),", "," and ",
IF(COUNTIF(A2:C2,"YES")-1=0,"1",COUNTIF(
A2:C2,"YES")-1))

CodePudding user response：

For Excel 2019, try:

Formula in F2:

=TEXTJOIN(" and ",,IF(A2:C2="YES",A$1:C$1,""))

Confirm through CSE and drag down.

Or;

=SUBSTITUTE(TEXTJOIN(", ",,IF(A2:C2="YES",A$1:C$1,"")),", "," and ",MAX(1,COUNTIF(A2:C2,"YES")-1))