Return Multiple Values based upon Multiple Criteria

[litlmike]

I have a roster of students that tracks their Group assignment (A, or B), and if they are Absent (a "Y" in the absent column). I would like to return a list of all students in Group "B" that are also Absent".

I thought that the follown formula would work, but it does not. Thank you for your help.

=INDEX('[Roster Master.xlsx]PM'!$A$1:$N$28,SMALL(IF(('[Roster Master.xlsx]PM'!$J$1:$J$28="Y")&('[Roster Master.xlsx]PM'!$K$1:$K$28="B"),ROW('[Roster Master.xlsx]PM'!$K$1:$K$28)),ROW(1:1)),1)

 

[jrg24]

I would use a pivot table. assuming your data has a column structure similar to Student Name| Group Assignment| Absent, I would pivot the data, put student names in the rows group, add both Group and Absent to the filters section. Filter for just group B and Filter Absent for "Y"

 

[litlmike]

I would use a pivot table. assuming your data has a column structure similar to Student Name| Group Assignment| Absent, I would pivot the data, put student names in the rows group, add both Group and Absent to the filters section. Filter for just group B and Filter Absent for "Y"

The problem with this solution is that the output has to be in a specific format, and I don't think that Pivot Tables will do that. Is there no way to just modify my current formula?