Google Sheets Select text from cell and export header names from corresponding column-CodePudding

I have a list of student names in a column and assessments names as a header. Along side each student I have a dropdown with either:

I am basically trying to extract text/data when the dropdown text 'NS' and/or 'MIS' is selected. Then using textjoin and placing the assessments names in the row next to the student.

I have managed to do this using a tickbox but needed to now do this with actual text rather than TRUE or FALSE:

The code for the second example:

=TEXTJOIN(", ",TRUE,ARRAYFORMULA(IF(B3:F3, $B$2:$F$2, "")))

So for example, in the first screenshot I would like 'Assessment 3', 'Assessment 4', and 'Assessment 5' placed in the row G3 for Joe.

I hope this makes sense.

Cheers

CodePudding user response：

One option that comes to mind:

Formula in G3:

=INDEX(TEXTJOIN(", ",1,FILTER(B$2:F$2,REGEXMATCH(B3:F3,"(?:MI|N)S"))))