For a website internationalization project, I have a Google Sheets with countries and languages that we would like to offer within that country.

Shortened sample sheet:

update:

=INDEX(TEXTJOIN(",", 1, INDEX(IFERROR(SORT(SPLIT(FLATTEN(
 IF(B2:D4>0, A2:A4&"-"&B1:D1&"​"&B2:D4&"​"&ROW(B2:D4), )), "​"), 3, 1, 2, 1)),,1)))

CodePudding user response：

Otherwise you can use:

=ARRAYFORMULA(TEXTJOIN(",",1,MID(SORT(FLATTEN(IF(B2:D4<>"",ROW(A2:A4)*1000 B2:D4&A2:A4&"-"&B1:D1,""))),5,100)))

create a sort order by value in the range, then join them.