Google Sheets - Query to create "Master summary" from another sheet when master sheet is set to condition

Question

I'm struggling a bit trying to write a query.

I have a Google sheet (here's a copy) That I need to write a query for in order to create a "master summary." The column in question is column U on the Master tab. Basically, I need to Import columns C D E and F from the sheet that matches then name in Column T, for the selected name that matches the row.

I know that I need to Query the individual sheet and import info with something like

=QUERY(MASTER!T:T,"SELECT MIRANDA! C,D,E,F WHERE MASTER!T:T ='Miranda'",0)

But I'm not getting anywhere with that. My other thought is just running a SELECT function like

=SELECT MIRANDA (C,D,E,F) WHERE MASTER!T:T ='Miranda'

But that's wrong too :) I suspect my syntax is off, as I am still learning SQL. And I believe Google apps use a slightly modified version anyways. What am I doing wrong?

Answer 1

The first part of the query function is the range to query against. The second part is what to grab. So I think you may be looking for this:

=QUERY(MASTER!A:T,"SELECT C,D,E,F WHERE T ='Miranda'",0)

and you will not receive any of the column headers as the 0 says to not use any row as a header.

This will list columns C, D, E, and F for rows where T is Miranda.

EDIT:

Since you are not showing the Timestamps in each of the individual sheets, it will be tough to get a single match, but Column M should be fairly safe to check against as long as it is required in your form.

=FILTER(MIRANDA!C2:F, MIRANDA!C2:C=M2)

will return an array of cells, C through F, for the row in the Miranda tab matching the value of M2. As is, it will place these in the column containing the formula and the next 3 as well. To get them in one cell, you need to Join them:

=join("|",FILTER(MIRANDA!C2:F, MIRANDA!C2:C=M2))

This will put the | character between each column item. Change "|" to what you would like it to be.

So to make this work by getting the value of cell T, you need INDIRECT():

=join("|",FILTER(INDIRECT( T2 & "!C:F"), INDIRECT( T2 & "!C:C")=M2))

Another option, especially if you included the timestamp in each tab, would be to use vlookup to look up the timestamp column. It may not work as well with the use of column M, though:

=VLOOKUP(M2, INDIRECT( T2 & "!C:F"), 1, FALSE)

Since you are using a custom script, you could also use one to copy the formula down, as you cannot apply an arrayformula() to any of these methods. I believe there are a few example scripts which do this. The problem is getting the Range by referencing another cell. The only way I know to do this is with the INDIRECT() function and it does not support arrays. Another option would be to write a custom formula that returns the range value you need and works with an arrayformula.