Try the following:
For Days On:
Formula:
=IF(A6="","",INDEX(Schedule!C$2:CE$2,AGGREGATE(14,6,(COLUMN(C$2:CE$2)-COLUMN(B$2))/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>1)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>3)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>4)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>7)/(Schedule!C$2:CE$2<=B$3),1)+1))
For Days Off:
Formula:
=IF(A6="","",INDEX(Schedule!C$2:CE$2,AGGREGATE(15,6,(COLUMN(C$2:CE$2)-COLUMN(B$2))/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>1)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>3)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>4)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>7)/(Schedule!C$2:CE$2>=B$3),1)-1))
Let us know if you have any questions.
Bookmarks