Find text and copy entire cell data from same source data?

[SharonP]

It seems simple to me, but apparently it's not. I have a list of teachers at a school (Row 1). In each of their columns, I have listed the subjects they teach. Now I want Excel to find all cells where "Spanish" is mentioned, and then copy the entire contents of that cell in the rows below the word Spanish in Row 7. That way I can see in which grades Spanish is taught. Same for all the subjects. I've tried IF, SEARCH, LOOKUP, INDEX+MATCH. None of them work consistently. Obviously I can eyeball the data and get the answers quickly. But I want to learn how to do this in case I have a much bigger dataset in the future. Thank you for any help you can provide.

 

[vletm]

SharonP
Before someone would give formula solution ...
You could press [ Do It ]-button and
maybe see 'an animation' for Your solution...

 

[SharonP]

SharonP
Before someone would give formula solution ...
You could press [ Do It ]-button and
maybe see 'an animation' for Your solution...

That sounds good. Where is the [Do It] button? I'm using Office 365 for Mac.

 

[vletm]

SharonP
Below photo tells more than 1000 words ...

 

[bosco_yip]

Maybe try this formula solution.

In A8, copied across to L8 and all copied down :

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,ROW($2:$5)/1%+COLUMN($A:$N)/($A$2:$M$5<>"")/ISNUMBER(FIND(A$7,$A$2:$M$5)),ROW($A2)),"R0C00"),),"")

Regards
Bosco