Help with Dependent Drop Downs

Hello,

I'm currently trying to practice my excel skills, and i'm currently stuck with creating dependent drop down tables.

I'm trying to do a school schedule, in which every time I select the subject in cell A, in cell B it loads the corresponding Subject Code, and in cell C it shows me the teachers which are teaching each subject.

If the subject is only one word, I can create the dependencies A and C successfully (Test no2) (using the INDIRECT formula)

If the subject has multiple words, even though I've tried using the INDIRECT formula together with SUBSTITUTE (as suggested in most places I've seen talking about this), i'm still unable to do it successfully.

And what's the best way of loading the data into B?
The only way I can think of right now is doing a table like the one in which I load the teachers of each subject (but I know that's the wrong way of looking at it).


Later on i'll try to create a list showing the amount of hours that the teacher has available for lecturing, and the total of hours already lectured, but so far I'm still stuck on this

BTW, my formulas are in Portuguese, so if they don't work that might be the reason.

Thanks a lot everyone

Try

in F7

=INDIRECT(SUBSTITUTE($C7," ","_"))

in D7

=INDEX(Subject_Code,MATCH($C7,Subject,0))

try substituting ";" for ","