Return First Row where cell contains "YES"

Question

I'm trying to compile a list of teachers that have recommended students for certain classes. We have a massive spreadsheet of teachers and their recommendations. I'd like to have a column that compiles a list of teachers that have said "YES" for a given student. See the sample sheet for an example and the desired result (yellow). Thanks in advance!

--Hyde · Accepted Answer

Hi Benjamin,
 
Try something like this:
 
=iferror( join(", ", filter(E$1:$1, E2:2 = "Yes") ) )
 
See the new Solution sheet in your sample spreadsheet for an illustration.
 
Cheers --Hyde

z.. · Accepted Answer

Hi there,
I have proposed another solution that doesn't need to be dragged. You can see it in tab z cell D2:
=index(substitute(trim(flatten(query(transpose(if(E2:T16="",,E1:T1)),,9^9)))," ",", "))
I hope you find this helpful. :) 
z