Google Sheets - Query another sheet (Array, Transpose)

Question

I would like to return the 'Standard' (columns C4:AG4) and 'Name' (A) of those that have a 'No' in either the 'Pre' or 'Post' for each Standard (see: columns C4:AG4 on the 3-5h example).

The list of names are displayed on (A4:A28).

, image: 3-5th example. I have attempted to Transpose (from Horiz to Vert) the 'Standard' values in an Index Sheet, but I'm having trouble associated the 'Standard' for each student. For example, each student completes all 10 of the Multiple choice questions and either receives a Yes (Correct) or No (Wrong) answer.

I have provided an image example of what I'm expecting.

=UNIQUE(TRANSPOSE(QUERY('3-5th Data Entry Example'!C4:AQ4)))


Desired Output:

If Student has a 'No' in either the Pre or Post (from sheet: '3-5th Data Entry sheet', their name(s) are returned

Standard 1    Student Name 1
Standard 2    Student Name 2    
Standard 3    Student Name 3
Standard 4    Student Name 4 
Standard 5    Student Name 5

Any assistance would be greatly appreciated. Thanks

Dummy Spreadsheet

Answer 1

Here's one approach you may test out:

=index(split(tocol(if('3-5th Data Entry Example'!C8:V="No",scan(,'3-5th Data Entry Example'!C4:V4,lambda(a,c,if(c="",a,c)))&"|"&'3-5th Data Entry Example'!A8:A,),1,1),"|"))