Array Formula that calculates next value

[mikec82]

=INDEX(Data!$E:$E,MATCH($A2,IF(Data!$H:$H=Summary!E$1,Data!$A:$A),0))

An example of the array formula above would be: Return the Course Title if the Student Name is John Smith and the Department is English.

This works for the initial instance.

It only works for the initial instance though. So if John is enrolled in two English courses, it still lists the initial Course.

I have tried the two array formulas below, also with no luck. Any recommendations on where I'm going wrong?

=INDEX(Data!$E:$E,MATCH($A2,IF(Data!$H:$H=Summary!E$1,Data!$A:$A),1))
=INDEX(Data!$E:$E,MATCH($A2,IF(Data!$H:$H=Summary!E$1,Data!$A:$A),+1))

 

[Special-K99]

Try

=IFERROR(INDEX(Data!$E$1:$E$1000,SMALL(IF((Data!$H$1:$H$1000=Summary!E$1)*(A2=Data!A$1:A$1000),ROW($A$1:$A$1000)),ROW(A1))-(ROW(A$1)-1),1),"")
Array formula, use Ctrl-Shift-Enter

 

[mikec82]

This still works for the first entry, but if I pull it to the right, it continues to give me the same result.

Here's an example of what I'm getting:

Student / English / English / English / English
John Smith / Literature / Literature / Literature / Literature

Here is what I would like for it to display:

Student / English / English / English / English
John Smith / Literature / Composition / Eng. Lang. / Contemporary Literature

Here is what the Data sheet looks like:

Student / Course / Department
John Smith / Literature / English
John Smith / Composition / English
John Smith / Eng. Lang. / English
John Smith / Contemporary / English