Getting a list of header columns where the value is true-CodePudding

I apologise if this question appears simple, but I'm having trouble making it work.

I just want to know what days were each employee absent in the column G (the last column), for example I want it like:

I tried to apply some MATCH/FILTER and ARRAYFORMULA formulas, but did not crack the puzzle. Please, help.

CodePudding user response：

Try TEXTJOIN() and FILTER().

=IFERROR(TEXTJOIN(", ",1,FILTER($B$1:$F$1,B2:F2=1)),"")

For dynamic spill array, use-

=BYROW(B2:INDEX(F2:F,COUNTA(A2:A)),
 LAMBDA(x,IFERROR(TEXTJOIN(", ",1,FILTER($B$1:$F$1,x=1)),"")))

Divide the results into cells.

=ArrayFormula(IFERROR(SPLIT(
 BYROW(B2:INDEX(F2:F,COUNTA(A2:A)),
 LAMBDA(x,IFERROR(TEXTJOIN(", ",1,FILTER($B$1:$F$1,x=1)),""))), ", ", 0),""))