Add conditional to INDEX-MATCH

[litlmike]

I am using the following for a student roster to provide a short list of absent students. I want it to return all students that have a "Y" in the "Absent" column. How do I modify this? Thanks!

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

 

[Special-K99]

With student names in column A starting at A1 and "Y" for Absent in column B use this instead

=IFERROR(INDEX($A$1:$A$8,SMALL(IF(($B$1:$B$8="Y"),ROW($A$1:$A$8)),ROW(A1))-(ROW(A$1)-1),1),"")
Array formula, use Ctrl-Shift-Enter

Modify this formula to suit your spreadsheet

 

[litlmike]

Thanks! I will go try this out.

 

[litlmike]

This works perfectly! Thanks!

Now, how do I extend this with another conditional? What if I want all absent students that are also in Group "B"? Column E is titled Groups. To clarify, the command would "Return a list of all ABSENT students from GROUP B".

 

[litlmike]

**bump**