How do I Text join multiple cells in an array formula in google sheets
I'm having trouble with the last part of a formula on Google Sheets.
In one column (B) I have cells occupied with different themes. These could be singular or have multiple themes in the cell separated by a comma. I'm matching these themes to theme headings and then taking the cells next to it in Column A (which contains names), combining them in a cell together to create a list of names associated with the theme.
I have managed to do this successfully if the cell only contains one theme, however if there are multiple themes separated by a comma then it doesn't work. I'm currently using =ArrayFormula(textjoin(", ",true,(IF($B$3:$B$8=F2,$A$3:$A$8,""))))
I have tried loads of things and feel like the answer is simple, but can't quite put my finger on it.
Here is the sheet - https://docs.google.com/spreadsheets/d/1dcDPN34qy1zAVZMxidd_zsurMU0Nv4zuA0YtMGAiFB0/edit#gid=0
Thanks for your help!
Try in E3
=if(REGEXMATCH($B3,E$2),$A3,)
if you want to get all together
=textjoin(char(10),,query(arrayformula(trim(split(flatten($A$3:$A&"~"&split($B$3:$B,",")),"~"))),"select Col1 where Col2='"&E$2&"' ",0))
