Partial string matching to index output - Google Sheets

Question

I'm using gsheets to structure survey responsed. I've got the list of responses (an array, user could have selected multiple). I've then seperated the unique responses to group them. Next I wanted to isolate distinct responses for each grouping so I can fugure out the unique number of each distinct grouped responses. so I created new columns in my raw data table with each grouping and then I need a formula to generate a boolean output if the user selected a response in that group.

I've got the unstructured data in column A, the unique responses and their grouping in columns B-D. In column h2 i'm trying to match the line to the grouping in h1.

=if(ARRAYFORMULA(IFERROR(VLOOKUP(REGEXEXTRACT(A2, TEXTJOIN("|", 1, B:B)),B:D, 3, false)))=$H$1,TRUE,FALSE)

Using the above I'm able to do it using this staging sheet, but when I put it into the master doc (where the grouping is on a seperate sheet to the response and the ranges are alot bigger) - it doesn't work. I've put both formulas used side by side in column I and they are the same.

Sheet here : https://docs.google.com/spreadsheets/d/1Vc0s0wJ2hT7T_MmlqFpEME_9-Ya2ngFLdZJnGEFuEhQ/edit?usp=sharing

Any idea why - Is there a better way to do this?

Answer 1

You may try this in Cell_H2:

=index(makearray(match(,0/(A:A<>"")),counta(H1:Q1),lambda(r,c,if(iserror(xmatch(index(H1:Q1,,c),xlookup(split(index(A2:A,r),", ",0),B:B,D:D,))),,true))))

• Clear the range H2:Q of any existing data\formulas so as for the above formula to expand freely