ELL Classes

[knaimi]

I have a spreadsheet in excel 2016 that contains the names of all the students in my school. The sheet has the students’ names, student id (which is unique for each student). One of the columns in the spreadsheet is ELL (Column I) which refers to English Language Learner. If there is a “Yes” in that column, it means that the student is actually eligible to receive ESL services. The majority of the ELL students are scheduled to take one of the following classes: ELL 1A, ELL 1B, ELL 2A, ELL 2B, … , ELL 4A, or ELL 4B. Some of the ELL with “Yes” may not take any of these classes if their English language level is high.

I need to add a formula in Column H (Tab 1) where if there is a “Yes” in any of the cells in column I (ELL), then it should check the student ID (Column D) and go to another tab called “Schedule” and find the same student ID (Column C) to see if the students is taking any of the classes listed above and place that class in column G, if the student doesn’t have any of those classes, the formula should place “Yes” in column H. Notice that the student schedule has 6 lines, each line represent the periods the student is scheduled in

Please see Sample below of both tabs:

Tab 1
[TABLE="width: 685"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Column H[/TD]
[TD]Column I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]State ID[/TD]
[TD]Counselor[/TD]
[TD]Grade[/TD]
[TD]I.D.[/TD]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Gender[/TD]
[TD]ELL2[/TD]
[TD]ELL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8402983537[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20085309[/TD]
[TD][/TD]
[TD][/TD]
[TD]Male[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0336793134[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]20103578[/TD]
[TD][/TD]
[TD][/TD]
[TD]Female[/TD]
[TD]ELL2[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5626301405[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20103579[/TD]
[TD][/TD]
[TD][/TD]
[TD]Female[/TD]
[TD]ELL2[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6241097197[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20080804[/TD]
[TD][/TD]
[TD][/TD]
[TD]Male[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9624763666[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]20079035[/TD]
[TD][/TD]
[TD][/TD]
[TD]Female[/TD]
[TD]ELL3[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6979995275[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]20056273[/TD]
[TD][/TD]
[TD][/TD]
[TD]Female[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8176885858[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]20061292[/TD]
[TD][/TD]
[TD][/TD]
[TD]Female[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]8465135700[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]20054334[/TD]
[TD][/TD]
[TD][/TD]
[TD]Male[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

Tab 2
[TABLE="width: 583"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Counselor[/TD]
[TD]Grade[/TD]
[TD]Student ID[/TD]
[TD]Student Name[/TD]
[TD]Period[/TD]
[TD]Course-Section[/TD]
[TD]Course Title[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20085309[/TD]
[TD]Student 1[/TD]
[TD]1[/TD]
[TD]613130-11[/TD]
[TD]Physics S1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20085309[/TD]
[TD]Student 1[/TD]
[TD]2[/TD]
[TD]612070-4[/TD]
[TD]US Economics[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20085309[/TD]
[TD]Student 1[/TD]
[TD]3[/TD]
[TD]618640T-1[/TD]
[TD]Auto Brake/Steering 5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20085309[/TD]
[TD]Student 1[/TD]
[TD]4[/TD]
[TD]618640T-1[/TD]
[TD]Auto Brake/Steering 5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20085309[/TD]
[TD]Student 1[/TD]
[TD]5[/TD]
[TD]611050-2[/TD]
[TD]Language Arts 5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20085309[/TD]
[TD]Student 1[/TD]
[TD]6[/TD]
[TD]614029-7[/TD]
[TD]Algebra 2 B S1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]20103578[/TD]
[TD]Student 2[/TD]
[TD]1[/TD]
[TD]614820-31[/TD]
[TD]Algebra 1 S1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]20103578[/TD]
[TD]Student 2[/TD]
[TD]2[/TD]
[TD]611836-224[/TD]
[TD]ELL 2B[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]20103578[/TD]
[TD]Student 2[/TD]
[TD]3[/TD]
[TD]611804-23[/TD]
[TD]Reading Improvement[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]20103578[/TD]
[TD]Student 2[/TD]
[TD]4[/TD]
[TD]611836-224[/TD]
[TD]ELL 2B[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]20103578[/TD]
[TD]Student 2[/TD]
[TD]5[/TD]
[TD]612833-55[/TD]
[TD]World History S1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]20103578[/TD]
[TD]Student 2[/TD]
[TD]6[/TD]
[TD]613800-76[/TD]
[TD]Biology S1[/TD]
[/TR]
</tbody>[/TABLE]