0

I have a dataset in which the right 5 columns indicate with a Y/N if each row applies to that option of 5 potential options:

Table example

I am looking to add one final column that uses a formula to check for each of the five options if it applies, and then produces a comma-separated list of which apply

E.g.

Row 1 [Y,N,N,Y,N] > output "Option 1,Option 4"

Row 2 [Y,Y,Y,N,N] > output "Option 1, Option 2, Option 3"

Or, a formula for the yellow cells in this image:

Desired output

I've tried various forms of array formulae but can't get it going...

Improve this question
3
  • Welcome! You didn't specify your version of Excel - does it have a TEXTJOIN() function?
    – JohnSUN
    Commented Oct 27, 2022 at 11:14
  • As indicated by JohnSUN's comment, this can be accomplished most simply using the TEXTJOIN() and (optionally) FILTER() functions. If you don't have those functions, it's still possible but will be a longer formula. =TEXTJOIN(", ",TRUE,IF(A2:E2="Y",A$1:E$1,"")) or =TEXTJOIN(", ",TRUE,FILTER(A$1:E$1,A2:E2="Y")) would work if you adjust the referenced ranges to fit your data.
    – Engineer Toast
    Commented Oct 27, 2022 at 12:04
  • That's perfect - didn't know that existed!
    – JakeyG
    Commented Oct 27, 2022 at 13:31

1 Answer 1

Reset to default
0

Answer is

=TEXTJOIN(", ",TRUE,FILTER(A$1:E$1,A2:E2="Y"))

(per comment from Engineer Toast above - thank you!)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .