Index Match for when Values in a column are repeated

[TerryE123]

Hi everyone
I was hoping you could point out either where I am going wrong or if I should take a different approach with this.

Attached is a small sample
On the Courses tab, I can populate a timetable across a row of dates to show when courses are being run.

On the second tab are a list of trainers with a similar layout to the first tab. Using the formula shown, I can get the course name in the correct column against the correct trainer whether that trainer is in the first or secong Trainer column (some courses need 2 trainers)

The problem I cannot resolve is when the trainer appears again for a different course on a different date. In the example attached, the 2nd course run by Trainer 1 and 3 is not showing.
I would be grateful is someone could point out what I am doing wrong or what I should be doing?

The real file will contain 30 trainers with over 300 courses a year.

Thank you all in advance

Terry

 

[hagia_sofia]

You could e.g. use FILTER function:

=CONCAT(FILTER(FILTER(Courses!$G$3:$N$6,((Courses!$E$3:$E$6=$B9)+(Courses!$F$3:$F$6=$B9))),Courses!$G$2:$N$2=C$8))

 

[TerryE123]

You could e.g. use FILTER function:

=CONCAT(FILTER(FILTER(Courses!$G$3:$N$6,((Courses!$E$3:$E$6=$B9)+(Courses!$F$3:$F$6=$B9))),Courses!$G$2:$N$2=C$8))

Thank you so much, hagia_sofia, that worked perfectly.