Trying to pull data from cell based on criteria in another cell, while skipping blank cell

I am working on a sheet involving various placement data. I want to have individual sheets for each class which includes only the students who place into that class and ignores students who do not place into that class; however, my Excel knowledge does not go far beyond IF or INDEX and MATCH. I cannot figure out how to use a formula to skip cells/rows that do not meet the criteria.

Sample sheet is attached.

The Overview sheet houses all placement data. On ENG 121 I want to only pull names that have "Yes" under ENG 121 on the overview sheet. The closest I know how to get is an IF function, but that returns 0 for kids w/o Yes. I need it to skip them.

In H2, I2 and J2

=IFERROR(INDEX(Overview!$E:$E,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

=IFERROR(INDEX(Overview!$A:$A,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

=IFERROR(INDEX(Overview!$B:$B,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

Enter all above with Ctrl+Shift+Enter


Copy down

Originally Posted by JohnTopley

In H2, I2 and J2

=IFERROR(INDEX(Overview!$E:$E,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

=IFERROR(INDEX(Overview!$A:$A,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

=IFERROR(INDEX(Overview!$B:$B,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

Enter all above with Ctrl+Shift+Enter


Copy down

This solved it. Thank you so much. Is there a resource where I can see the logic of this formula explained? I understand the INDEX MATCH logic and the basic logic of formulas, but I cannot figure this one out.

Thanks again