Situation: I have 2 sheets where I am working in.

• Sheet1: Has the point metrics of all the Google Form responses for each person. There are 4 Forms. This sheet has 4 columns with that users' score and one Boolean (True/False) column called "Passed?" that's meant to identify if the user scored higher than 75% (among many other metrics that I'm leaving out). Example below.
• Sheet2: Is supposed to only list the names of the highest point scorers, so I can send them a reward or special message. I do this process manually currently.

Question: How might I be able to pull in every row of cells to access just specific cells in that row (ie. "Email" & "Score") of a different Sheet, based on a true or false column? How can I automate this process of identifying the winners and putting them in Sheet2?

Expected Result: In Sheet 2, I include the email, first name, last name, and score.

I just have a general idea of how I want to do it:

• First fill in "Email" based on "Passed?" column in Sheet1, then repeat for "Score".
• Then for the "names" columns, I do an index match to get those based on "Email" from one of the Form responses sheet linked in this Spreadsheet.

Any help would be greatly appreciated.

CodePudding user response：

Based on the layout of your sheets in screenshots; you can try:

=QUERY(IMPORTRANGE("SPREADSHEET ID","Sheet1!A:L"),"SELECT Col2,Col10 Where Col12=TRUE")