Google-sheets – Why is Google Form stuck on “Updates made after release”

I added a sheet called SO Test - Aurielle where you can view my results

So here is my suggestions - it requires 2 formulas and you would need to copy one of the formulas down as needed but otherwise its pretty simple:

In column A you enter this formula:

=UNIQUE(ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(JOIN(",",Sheet1!A:A),",")))))

What I am doing here is first joining all the values with a common delimiter, in this case a , so it creates one long string, then splitting by that same delimiter to create a long list of all possible keywords. I use trim to clean it up and remove any unnecessary formatting, or space.

I then use UNIQUE to get a list of all possible keywords.

In Column B i entered:

=AVERAGEIF(ARRAYFORMULA(REGEXMATCH(Sheet1!A:A,A2)),"true",Sheet1!B:B)

What this does is check each value in column A to see if it contains the keyword to the left of it, REGEXMATCH is great for this because it globally checks whether that word is at all contained in the original string, ignoring any other characters or punctuation.

By using ARRAYFORMULA it converts the values to true or false, so if you were to expand and just show that formula by itself, it would say true,false,true, true, because food is contained in the 1st string, but not the 2nd, and is in the 3rd and 4th.

Using AVERAGEIF, we use that array as the condition to check, but direct it to the column next to it, as the condition to average.

This is done by vlookup. A basic example is

=vlookup(B2, Sheet2!B2:C, 2, False)

which says: find the value of B2 in the first column of the range Sheet2!B2:C, and return the corresponding value from the 2nd column of that range. (The last argument, False, says the searched range need not be sorted, and exact match is required.)

A more advanced version, for looking up all students at once:

=arrayformula(iferror(vlookup(filter(B2:B, len(B2:B)), Sheet2!B2:C, 2, False)))

Additional elements here:

• this is an arrayformula, which processes lookups for an array of data
• the array to process is B2:B of the current sheet, but it's filtered so we don't waste time looking up empty-string values
• the wrapper iferror suppresses error messages #N/A when no match is found; the Quiz score cell will be blank if the student never submitted the quiz.