Create Table based on data entry in cell

This should be largely doable. Start by changing B12 from "Course Number" to "Course" so that it matches the header on the first sheet. Then, in B13, use the formula below; it should be array-entered (confirm with Ctrl + Shift + Enter instead of just Enter):

=IFERROR(INDEX('Program of Study'!$A$8:$H$73,SMALL(IF('Program of Study'!$G$8:$G$74=$B$3,ROW('Program of Study'!$G$8:$G$74)-ROW('Program of Study'!$G$8)+1),ROW(1:1)),MATCH(B$12,'Program of Study'!$A$6:$H$6,0)),"")

Fill right through G13 for the rest of your table. You can turn the "Repeat Course" column into a yes/no drop down with data validation. For "C or Better?" replace the formula in F13 with the following (non-array) formula:

=IF(B13="","",IF(OR(LEFT(B13,4)="HPED",LEFT(B13,3)="GCS"),"YES","NO"))

Fill down as far as you think you'll need to. Take a look at the attachment to see if it works for you:

I'm glad we could help, good luck!

CAntosh or kersplash:

In using the suggestion you gave me I found a scenario. What you suggested works fine if a student follows the program of study to the letter. However, in a situation where a student takes a course that will be used to substitute for the course (in column A of the Program Study), I would like the substituted course (in column E or column F of the Program of study) to be entered in the Course Schedule worksheet (in column B).

See the Course Schedule_Current and Course Schedule_Expected worksheets. In the example, the Course Schedule worksheet is used to search the courses where the student enrolled in the course during the 201910 term (cell B3 in the Course Schedule worksheet). The results are passed under the Course, Course Name, Credits, Repeat Course?, and C or Better? "columns" in the Course Schedule worksheet. Currently (in Course Schedule_Current), the Course column is populated with the Courses that are in column A of the program of study where Cell B3 (Course Schedule) is equal to or matches entries in column G of the program of study.

I need the Course Schedule worksheet to first check to see if columns E and F are empty/blank. If they are blank, then the course in column A (program of study) is returned to column B (Course Schedule). If the cell in column E is not blank and column F is blank (Program of Study) then return the value in column E (program of study) to column B (course schedule). If column F is not blank (Program of Study), then return the value in column F (Program of Study) to column B (course schedule).

See the attached file with the Program of Study, Course Schedule_Current and Course Schedule_Expected. I have highlighted the salient cells and rows for illustration.

That makes things a bit uglier, but it should still be doable. Try the following in B14; it should be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

=IFERROR(INDEX('Program of Study'!$A$8:$H$73,SMALL(IF('Program of Study'!$G$8:$G$74=$B$3,ROW('Program of Study'!$G$8:$G$74)-ROW('Program of Study'!$G$8)+1),ROW(1:1)),MAX((ROW('Program of Study'!$A$8:$H$73)=SMALL(IF('Program of Study'!$G$8:$G$74=$B$3,ROW('Program of Study'!$G$8:$G$74)),ROW(1:1)))*(NOT(ISBLANK('Program of Study'!$A$8:$H$73)))*({1,0,0,0,5,6,0,0}))),"")

...and fill down. If I'm understanding the objective correctly, then you can leave the formulas in C:F as they are. Take a look at the attachment to see if it's working as desired: