I have an employee schedule that shows the status of each employee defined by their "position" (i.e. day, day lead, night, etc.) or if they are on leave. Blanks represent off days. On the second tab of the worksheet, there are two functions I would like to utilize index/match for. The first is to be able to look up a lead position such "Day Lead" (SheetSchedule!$B$3:$G$14) and a specific day (SheetSchedule!$B2:$G$2) and return the name of the employee who will be working. The second is to look up the leave or off status and a specific day and return the name of the employee(s) who will be working.
To look up the position, I have tried the following formula in various ways with no success:
=INDEX(SheetSchedule!$A$2:$G$14,MATCH($C$2,SheetSchedule!$B$2:$G2,0),MATCH($C$3,SheetSchedule!$B$3:$G$14,0))
For the second portion:
=IF(ISERROR(INDEX(SheetSchedule!$A$2:$G$14,SMALL(IF(SheetSchedule!$B$3:$B$14=$F$3,ROW(SheetSchedule!$B$3:$B$14),ROW(SheetSchedule!1:1)),1)),"",INDEX(SheetSchedule!$A$2:$G$1,SMALL(IF(SheetSchedule!$B$3:$B$14=$F$3,ROW(SheetSchedule!$B$3:$B$14)),ROW(SheetSchedule!1:1)),1))
The issue with this is that I can't figure out how to make it look up the date as well and then look within the range "SheetSchedule!$B$3:$G$14" to find what I put in F3.
I'm still learning how to build complicated excel formulas so any help would be greatly appreciated! I attached a simplified version of my actual schedule but it should correspond with the fields I put above as well as an example of the output I want. Thank you!
Bookmarks