Get column names as comma-separated list for the column where value greater than 0 in Excel

Question

I have a table with columns as below

Name	List	Apple	Orange	Mango	Banana	Cherry

Name contains the names of the vendors.

Apple Orange Mango Banana Cherry contains the QTY like 1,10,15.....

This is what I need

List name of fruits where the value is greater than 0 as [Apple, Orange]

Do let me know if things are not clear.

Thank you in advance for answering the question

I tried VLookup but not sure if I am making any mistake

=LOOKUP(2,1/C3:AC3,C$2:AC$2)

In this, I need to set it for each Column I need a Dynamic Column

=SUBSTITUTE(TRIM(CONCATENATE(IF(C3>0,$C$2 & ",",""),IF(D3>0,$D$2& 
",",""),IF(E3>0,$E$2& ",",""),IF(F3>0,$F$2& 
",",""),IF(G3>0,$G$2,","))),",",", ")

I am using the latest version of Excel on the web

Answer 1

If you have Excel 365 you can use this formula

=TEXTJOIN(", ",TRUE,FILTER($C$1:$G$1,C2:G2<>""))

Answer 2

Try using TEXTJOIN() with IF() --> Works with Excel 2019+ onwards.

---

=TEXTJOIN(", ",1,IF(C2:G2<>"",$C$1:$G$1,""))

---

Or Use Single dynamic array formula to spill --> Works with MS365.

=BYROW(C2:G3,LAMBDA(x, TEXTJOIN(", ",1,IF(x<>"",$C$1:$G$1,""))))

---

Note: Excel 2019 Users needs to hit CTRL+SHIFT+ENTER while exiting the edit mode which is only applicable to first formula.

---

Answer 3

With the traditional excel functions has to use as many additional column as fruits named Help1 to Help5 formula in W22 and drag to right.

=V22&IF(Q22<>"",Q$21&",","")

Formula in P22 and drag down

=LEFT(AA22,LEN(AA22)-1) AA22 is the last column (Help5)