In the first formula, we use FILTER() on the TRAINEES table to get only the rows that match the location. The location can be found with the last 4 letters in cell E10 on the Schedule sheet. This, however, returns all the columns, so we apply again FILTER(), using an array {1,1,0} to get only the first two columns. This formula will spill down and on the right.
For columns G to P, the informations are in the table ROLES, we only need to match the Role and the Training Topic, then TRANSPOSE() the array returned by INDEX(). This formula will spill to the right, and must be dragged down.
For the last table, a regular MATCH() looking for Location and Topics on the Presenters table is enough.
Btw, I just realized I missed one request for marking the presenters, here is the formula for G9, then drag to the right
Formula:
=INDEX(Info!$F$17:$H$26,MATCH(Schedule!G2,Info!$E$17:$E$26,0),MATCH(LEFT(Schedule!$E2,4),Info!$F$16:$H$16,0))
then you can copy/paste to rows 17 and 25.
Thx for the feedback and added rep. 
HTH,
Francesco
Bookmarks