Search code examples
google-sheetsselectdrop-down-menugoogle-sheets-formulatextjoin

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

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:

  • NS
  • S
  • MIS

enter image description here

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:

enter image description here

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

Solution

  • One option that comes to mind:

    enter image description here

    Formula in G3:

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