Array formula to join header values in a Google Sheet when row values equal "yes"

Viewed 64

I saw this question but was not able to get it to work for me. Since it is already answered and from an year ago I felt it best to post a new question.

I have a sample sheet at: https://docs.google.com/spreadsheets/d/1EqNfTWcNFgkv2gdovnH7JpqU9V2HG5Vkoup8bhNqI7k/edit#gid=0

In F1 I want an array formula that will join the values of B1:E1 if B2:E = "yes".

Column G has the expected output.

This is what I came up with but it's giving error:

={
    "output";
    ARRAYFORMULA(
        IF(
            A2:A = "",
            ,
            IF(
                B2:E = "yes",
                TEXTJOIN(", ", TRUE, B1:E1),
            )
        )
    )
}
2 Answers

Try this out

=index(substitute(trim(transpose(query(transpose(if(B2:E<>"yes",,B1:E1)),,9^9)))," ",", "))

If the header contain spaces, use:

=index(substitute(substitute(trim(transpose(query(transpose(if(B2:E<>"yes",,substitute(B1:E1," ","❄️"))),,9^9)))," ",", "),"❄️"," "))

use:

=INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(B2:E="yes", B1:E1&",", )),,9^9))), ",$", ))

enter image description here

Related