kksweene
kksweene

Reputation: 9

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

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

Upvotes: 1

Views: 1275

Answers (2)

JvdV
JvdV

Reputation: 75870

For Excel 2019, try:

enter image description here

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))

enter image description here

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27273

For 2019, use some alternative approaches.

FORMULA_SOLUTION

• Formula used in cell D2

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

FORMULA_SOLUTION

• 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))

enter image description here

• 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_SOLUTION

• 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))

Upvotes: 3

Related Questions