Google-sheets – Find column headers then do multi-criteria lookup

google sheetsgoogle-sheets-query

I'm trying to create a report where I need to find each column by header, then do a multi-criteria lookup. (I need to find the proper column because the data file is a csv export from another application, and that may change)
I want to get the list of unique participants from a chosen class in a chosen month and year (for the columns of the end report)

I assume there is some way with nested Index/Match or a way with Query, but I have not been able to get it. 

+------------+-----------------+-----------+------------+
| AttendDate | ParticipantName | ClassName | Attendance |
+------------+-----------------+-----------+------------+
| 10/1/2017  | A               |         1 | P          |
| 10/1/2017  | B               |         1 | P          |
| 10/1/2017  | C               |         1 | P          |
| 10/1/2017  | D               |         2 | P          |
| 10/1/2017  | E               |         2 | A          |
| 10/1/2017  | F               |         2 | P          |
| 10/2/2017  | A               |         1 | P          |
| 10/2/2017  | B               |         1 | P          |
| 10/2/2017  | C               |         1 | A          |
| 10/2/2017  | D               |         2 | A          |
| 10/2/2017  | E               |         2 | A          |
| 10/2/2017  | F               |         2 | P          |
+------------+-----------------+-----------+------------+

So far I have the following which isn't complete yet:

=transpose(unique({index(data!$A2:$BA,0,match("ParticipantName",data!$A1:$BA1,0)),index(data!$A2:$BA,0,match("ClassName",data!$A1:$BA1,0)),index(data!$A2:$BA,0,match("AttendDate",data!$A1:$BA1,0))}))

OR a query which isn't working right:

=query(data!$A:$BA,"select 'ParticipantName' where ('ClassName'='"&$B$3&"' and month(DATEVALUE('AttendDate'))="&$B$1&" and year(DATEVALUE('AttendDate'))="&$B$2&")")

For Class 1, October 2017, I want the output to be

+---+---+---+
| A | B | C |
+---+---+---+

Best Answer

Partial answer

The initial intention of this answer is to share some hints and information about the mentioned functions.

INDEX / MATCH

We could use a MATCH to find the index of column and then use that index to set the column to be used to do the next lookup. This index could be use in a function like OFFSET.

QUERY

QUERY syntax allow the use of sheet column headers (A,B,C, etc) or Col1 notation but not it not supports the use of "field names" / column header labels.

Related Topic