Lookup a value across multiple columns and post a value from the column header

[wdgor]

The spreadsheet has multiple columns with headers. Each row has an answer to that column, i.e. "Yes", "NO", "Blank" or some other text. I want to test each row in the column for Yes" and put the column header in Column O as an "accumulator", i.e. if there is a "Yes" in column A, column O would have the column header "A" listed. If column B has a "Yes", column O would now have, "A,B". In the example, column O would look like A,B,C,D,F,H,M (comma separated).

I tried: =IF(A2="Yes",$A$1,IF(B2="Yes",$B$1,IF(C2="Yes",$C$1,IF(D2="Yes",$D$1,IF(E2="Yes",$E$1,IF(F2="Yes",$F$1,IF(G2="Yes",$G$1,IF(H2="Yes",$H$1,"")))))))) etc. but the formula just returned the first "Yes" value of A in column O and ignored all the other Yesses. I understand why that happened as the If statement found the first true value and posted it. I am not sure how to have it evaluate each value in row 2 and look for a "Yes" and take the value of the column header and comma delimit it in column O to get the A,B,C,D,F,H,M that represents each "Yes" with its corresponding column header. Any assistance is appreciated. Thanks.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

Yes

Yes

Yes

Yes

Yes

No

Yes

Windows

No

Yes

 

[etaf]

not sure what version of excel you have - if the later version then
=TEXTJOIN(",",,FILTER(A1:N1,A2:N2="yes"))

Not sure for previous version of excel, can you update your profile with what version of excel you are using

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	A	B	C	D	E	F	G	H	I	J	K	L	M	N		Result
2	Yes	Yes	Yes	Yes		Yes	No	Yes	Windows			No	Yes			A,B,C,D,F,H,M
Sheet1

Cell Formulas
Range	Formula
P2	P2	=TEXTJOIN(",",,FILTER(A1:N1,A2:N2="yes"))

 

[wdgor]

I will update my profile as I am using 365.

Your solution worked perfectly with the TEXTJOIN. Thank you very much. I have used it in the past but didn't think to apply it to this issue. Helped a lot - thanks again.

 

[etaf]

you are welcome