INDEX/MATCH using 2 criteria, and returning Nth value of INDEX/MATCH with 2 criteria

[GJG]

Hello,

I have a table in which people are assigned an amount of time to a task (in this case, teaching a course).  The first table is organized by person; their name repeat in column A, with courses they are assigned to teach in column B, and their role (coordinator "C" or assistant "A") listed in column C.  They are then given a workload in hours in column D.  The attached image and dummy file shows this in columns A-D.  

What I'm trying to do is produce a second table organized by course, which retreives data from the firs to show who is coordinating ("C") and who is assisting ("A") each course.   This is shown in the attached image and dummy file in the gray cells (columns F-J), where the black text are things I enter and the red is what I want a formula to retreive (currently I have just typed in the results to illutsrate the point). 

For column G, I need to INDEX/MATCH 2 values: the name in column F from column B, and "C" from colun C, and return the value in column A.  I have tried adding an AND() function to the INDEX/MATCH pairing, but it does not work.  I've also tried variations of VLOOKUP without success.  

For columns H-J, I want it to return the value in column A based on the Nth time column F mathces column B and "A" matches column C.  So, column H will return the 1st value found in column A whenever the value in column F matches column B and "A" matches column C.  Column I would then return the 2nd value found that matches the same criteria, and column J the 3rd value found. 

I'm sure this will require different formulas in columns G and H-J.  Can anyone help?  

[#2]

@GJG 

Either with a data model pivot table and a measure with CONCATENATEX() or with dynamic array formulas.

[#3]

@GJG 

Here's an altogether different way to do this, using the newly available Dynamic Array functions UNIQUE and FILTER.

The cell with the yellow background invites you to select the name of a course. Then the cells to the right will populate with the names of the staff members assigned. This happens automatically, and the only formulas are in cells G12 and H12. The results "spill" down to accommodate however many names meet the FILTER criteria.

Here's a YouTube video that explains the functions more fully.

https://www.youtube.com/watch?v=9I9DtFOVPIg

[#4]

@GJG 

For the collection Power Query solution

as

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"HOURS"}),
    #"Grouped Rows" = Table.Group(
        #"Removed Columns",
        {"COURSE"},
        {
            {"Tables",
            each 
                [RemoveCourse = Table.SelectColumns(_,{"ROLE", "PERSON"}),
                 SortRole = Table.Sort(RemoveCourse,{{"ROLE", Order.Descending}}),
                 TransposeTable = Table.Transpose(SortRole),
                 PromoteHeaders = Table.PromoteHeaders(TransposeTable, [PromoteAllScalars=true])
                ][PromoteHeaders]

            },
            {"Count", each List.Count(_)}
        }
    ),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Titles", each Table.ColumnNames([Tables])),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Count", Order.Descending}}),
    MaxRolesA = List.Max(#"Sorted Rows"[Count]),
    ColumnNames = List.Transform(
        {1..MaxRolesA},
        each
            if _ =1
            then "Coordinator"
            else "Assistance " & Text.From(_-1)
    ),
    Titles = #"Sorted Rows"[Titles]{0},
    RemoveUnused = Table.SelectColumns(#"Grouped Rows",{"COURSE", "Tables"}),
    ExpandTables = Table.ExpandTableColumn(RemoveUnused, "Tables", Titles, ColumnNames)
in
    ExpandTables