2

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 |
+---+---+---+
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.