I am trying to adapt a formula that was used across columns to use across rows. The formula I have no only returns one result which is the first item in column B.
I want the formula to 1) find a match to the student name on Sheet 1 (Marks) and then return the name of the course they are taking in an adjacent column on sheet 2 (Strugglers) but only if the mark is less than 60. 2) if there is another match to the student name on Sheet 1 it should return the name of the course from that match in the next adjacent column on Sheet 2 with the same mark criteria.
This is the formula I have so far. The problem I have is that it is only returning the course from the first cell in column B. Help! I am attaching a sample file.
=IF(COUNTIF(Strugglers!$A$2:$A$25,$A2)>=COLUMNS(B:$B),INDEX(Strugglers!$B$2:$B$25,SMALL(INDEX((Strugglers!$A$2:$A$25=Summary!$A2)*COLUMN(Strugglers!$A$2:$A$25),0),COLUMNS(B:$B)+COUNTIF(Strugglers!$A$2:$A$25,"<>"&Summary!$A2))),"")
Bookmarks