Remove the Absent Students from the List of Failing Students

Hello,

With the help of Etaf, a valued contributor of this forum, I could prepare an excel marksheet, with one worksheet containing a list the failing students. I then added a column to show whether students are present or absent. Now the absent students appear in the list of failing students and I want to remove those absent students from the list of failing students. Please help. I attached the worksheet in this thread.

Alya

In B3 use this array formula

=IFERROR(INDEX(Marks!$A$3:$E$51,MATCH(0,COUNTIF($B$2:$B2,Marks!$B$3:$B$51)+(Marks!$F$3:$F$51<>"F")+(Marks!$D$3:$D$51="Absent"),0),COLUMN(B1)),"")

Confirm with Ctrl+Shift+Enter