Excel - Return multiple column headings based on multiple "x" entries in a row in a table?

Question

If I have a table with various combinations of "x" in the columns of each row, against each row how do I write a formula that would return the column heading for each column where there is an "x". The cells without an "x" are blank, and would not return the column heading.

SO if my columns were called A, B, C and D and there was and x in column B and D only for that reference row, I would get a return of "B,D" as a comma separated string.

Thank you

question from:https://stackoverflow.com/questions/65870627/excel-return-multiple-column-headings-based-on-multiple-x-entries-in-a-row-i

Answer 1

With O365 you can use following formula:

=TEXTJOIN(",",1,FILTER(A1:D1,MMULT(COLUMN(A1:C1)^0,--(A2:D4="x"))))

In the MMULT function, the number of columns in the first parameter COLUMN(A1:C1) must correspond to the number of rows in the second parameter A2:D4="x"