I have a sheet that is populated by a form. On the form I have multiple questions and each question includes a bunch of options where they check off what they want. Because they're check boxes, when they submit the form, all the questions they choose populate into one cell where each question is separated by an comma.
My plan is to take that info and merge it into a Google Document; however what I want merged is a more detailed version of the question they answered.
Problem: The formula does what I need it to, however, I need it to work across multiple rows as new submissions come in and because I'm using =filter, it doesn't work with =arrayformula.
=concatenate("",filter('Look up Table'!B:B,search('Look up Table'!A:A,A2)))
Here is an example of what is happening.
Column A represents what has been submitted through the form. So the user chooses (with checkboxes) what is applicable. They have populated in one cell and are separated by commas.
Column B is my current formula that does a search by question and pulls in the formatted-version of the question (Look up Table tab). This is what merges into the document.
I'm hoping someone can help me find a new formula to use that does the same thing (and works with arrayformula) or perhaps lead me in another direction that I just haven't come across yet. I'm looking into vlookup and regexreplace to see if I can get that work as well.
Try this formula:
=ARRAYFORMULA(SUBSTITUTE(trim(transpose(query(IFERROR(vlookup((IFERROR(search(offset('Look up Table'!A1,,,COUNTA('Look up Table'!A:A)),TRANSPOSE(A2:A)))>0)*row(offset('Look up Table'!A1,,,COUNTA('Look up Table'!A:A))),{row(offset('Look up Table'!A1,,,COUNTA('Look up Table'!A:A))),offset('Look up Table'!B1,,,COUNTA('Look up Table'!A:A))},2,0)),,COUNTA('Look up Table'!A:A))))," "," "))
Look at example here