I am an administrator in a medical residency program. I am trying to develop and automated excel sheet to produce what we refer to as a "staffing report" for clinic coverage for any given day.

Individual schedules are listed in on Sheet1 in a horizontal manner. With residents names listed vertically down the sheet starting in A4, then A7, and on.

Schedule1.png

In Sheet2 there is a daily schedule with each assignment listed. Divided into AM and PM schedules.

Schedule2.png

Sheet2, B5 I want to display all of the residents assigned to AM "Women's" clinic for Wednesday 7/30.

I am an excel novice, and have tried multiple times with VLOOKUP and IF COUNTIF functions, but can't seem to get anything that works. I appreciate any assistance, as this will greatly improve our efficiency in scheduling resident clinics.