Lookups based on criteria across sheets

Question

I am trying to extrapolate data from a from response in forms/google sheets.

On the attachment, students can select 1, 2 or 3 in any of the columns. I then need the header of the 3 selections to be looked up on the second sheet in the correct column (reducing it down to everything just in the square bracket is a bonus).

This is an example, in the real thing there'd be more students in the list.

Thanks

Lookup Example

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, QUERY(SPLIT(FLATTEN(
 IF('Form responses 1'!C2:1000<>"", 'Form responses 1'!C2:1000&"×"&
 REGEXEXTRACT('Form responses 1'!C1:1, "\[(.*)\]")&"×"&'Form responses 1'!B2:B1000, )), "×"), 
 "select Col3,max(Col2) where Col3 is not null group by Col3 pivot Col1"), {2,3,4}, 0)))