Transpose row of irregular number of values to columns of matching indices

Question

I have a Google Form featuring an ID number and a checkbox field capable of generating multiple choices, anywhere from 1 to 4.
I export this data to a Google spreadsheet.

When multiple checkboxes are ticked, the output goes into a single field with comma separators. I've created a second sheet in which I split that data using Split('TAB_REFERENCE!A1',"TRUE, TRUE) into a sheet that looks like this:

I now want to query that data to display ID numbers associated with each of the elements, to look like this:

I've tried multiple boolean OR statements in a filter, Pivot tables, and a half dozen bad ideas.

I can't use ArrayForumula because the element count varies. I know this is solvable but as my headline suggests, I can't even articulate the problem succinctly enough to get good search results. It's like a transpose of part of a row into columns of unique values with the index value displayed under the columns it matches. Help!

Answer 1

I think this relatively simple single formula can do it without a separate tab to do the splitting. I also made a sample sheet here. On the MK.Help tab, you'll find this formula in cell B2.

=ARRAYFORMULA(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(IF(REGEXMATCH('Form Responses 1'!C2:C5,B1:E1),'Form Responses 1'!B2:B5,),,9^9))," ")))

Hopefully you can figure out which references correspond to the correct ones on your real sheet.

Answer 2

You can do it with several simple formulas if you can use multiple steps :

1. first you need to transpose the data

2. then add a row with the animals
3. use this formula =IF(COUNTIF(H$2:H$5,$G8),H$1,) ($ are very important for easy formula-dragging)
4. transpose a second time
5. you're done

Test sheet