Seeking Complicated Formula to fetch Data from 3 different Sheet

[Morshed_Dhaka]

Hello All,

I am seeking support about a complicated formula. Below is situation details :

1. Sheet 1 : Term Name = this sheet contains Days of the Week, From Time, To Time, Room wise sample Term Name
2. Sheet 2 : Class Name = this sheet contains Days of the Week, From Time, To Time, Room wise Subject/ Class Name
3. Sheet 3 : Teacher Assignment = this sheet contains Days of the Week, From Time, To Time, Room wise Teacher's Name
4. Sheet 4 : Teacher's Weekly Schedule = it shows Weekly schedule of a teacher for a whole week

The problem is i want to see Teacher Name on Column & it will fetch Term Name, Class Start Time & Class Name by Day wise from 1st 2 sheet into the sheet 4 automitically when the Teacher's name is avaialble in the Column header into a merged cell. I am trying so many different formulas but i failed.

Can sombdody help me ?? You can find out the sample file from the below link. It is only sample data. Nothing original is shared.

Link for the file : https://drive.google.com/open?id=1AG1izlYchwGRJQxnKiWhexA2bvDeZTFc

Thanks.