Assign teachers to students

Hi everyone,

I am trying to create and excel sheet to assign teachers to students based on the classes they teach. One teacher can be assign to a maximum of 50 students.

Student Name | Class |
John | A |
Mary | B |
Sally | C |
John | A |
Mary | B |
Sally | C |
John | A |
Mary | B |
Sally | C |

Teacher Name | Teaching Class |
Michael | A, B, C
Jessica | A
Wendy | B, C
Ronald | C

Results
Student Name | Class | Teacher Assigned
John | A | Michael
Mary | B | Michael
Sally | C | Wendy
Sean | A | Jessica
Paul | B | Wendy
Harold | C | Ronald
Tim | A | Jessica
Ben | B | Wendy
Steve | C | Ronald

Sorry I am new and this is too complex for me. I have read up about INDEX, MOD, and MATCH function but I do not understand how to implement all the functions together. I hope someone could help me with this. Thank you so much.

Regards,
John