Using V or HLookup to auto populate cells for large schedule

Hi Everyone!

I'm a very basic Excel user and am trying to figure out how I can work smarter and not harder with my Master Schedule. I handle the year's rotation schedule for our 45+ Medicine Residents and am looking for a way to auto populate information from their yearly schedule to other parts of the spreadsheet.

For example:
Each Resident has 4 rows and 15 columns; 1 row for each week in our block, and 1 column for each of 13 blocks (the other two columns are their names and description wk 1, wk 2, etc.) I will fill in this information as we create the schedule.

Further down the document, we have a list of Electives in the first column, the second column is blank and the 13 blocks in the corresponding columns as the schedule above. I'd like to auto populate the residents name into the correct elective and block when I add it to the residents schedule at the top.

So, when Resident 1 takes Pulm in block 4, I'd like Resident 1 to appear under Pulm.

I realize there are some complications to this, especially when residents take 2 weeks of 1 elective and 2 weeks of another in the same block, but if the majority of this can auto-populate it would make my life SO MUCH EASIER.

Please let me know if any of this needs any clarification! I've attached a test document below.

Thanks!!!



Excel Test.xlsx

Made those changes, please let me know if I need to fix anything else! Thanks

Excel Test.xlsx