Value from list depending on conditions that should start over once finished

[yomarcos]

Hello everyone,

I need your help.

I have a table of employees. I want to provide an alternative in case there is a clash with dates or amount of hours worked. I managed to get a formula that goes through my list of employees but it only goes through once. I want it to start over if there is no conflict.

The formula is for column J. It should work for both teams. I only used Team2 to make it easier to read

Example

 

[yomarcos]

My apologies. My explanation was not very clear.

I am looking for a formula for column J. I want 3 possible outcomes:

1. "Ok to Allocate" - when the preferred option on Column F is free during the period of time chosen between Column C and Column D for the amount of hours per day needed in Column 3.
2. Another person from the list of that team who is free during that period for those hours when the preferred option is not available (this is where a can't manage to get it done properly
3. "Contractor" - when nobody on the list is available during that period for those hours per day

Formulas I am using:

Column G (Conflict) =if((if(or(F2=0,A2=0),false,sumproduct((A2=$A$2:$A$13)*(F2=$F$2:$F$13))>1))=false,if(AND(SUMPRODUCT(($C2<=$D$2:$D$13)*($D2>=$C$2:$C$13)*($F2=$F$2:$F$13)*($B2=$B$2:$B$13))>=1,sum(if(H2>1,FILTER($E$2:$E$13,F2=$F$2:$F$13,H2=$H$2:$H$13),E2))>8),true,false),true)

Column H (Number of conflicts per person per period of time) =SUMPRODUCT(($C2<=$D:$D)*($D2>=$C:$C)*($F2=$F:$F))

Column I (Available hours per day for person chosen) =8-sum(if(H2>1,FILTER($E$2:$E$36,F2=$F$2:$F$36,H2=$H$2:$H$36),E2))

Column J (Alternative) =ArrayFormula(if($A2<>0, if($F2<>0,(IF($G2=False,"Ok to Allocate",if(INDEX(INDEX($A$16:$B$21,0,MATCH($B2,$A$15:$B$15,0)),SMALL(IF(ISNA(MATCH(INDEX($A$16:$B$21,0,MATCH($B2,$A$15:$B$15,0)),$F$2:$F$13,0)),ROW(INDEX($A$16:$B$21,0,MATCH($B2,$A$15:$B$15,0)))-ROW($A$16)+1),COUNTIFS($B$2:$B2,$B2,$G$2:$G2,$G2)))="","Contractor",INDEX(INDEX($A$16:$B$21,0,MATCH($B2,$A$15:$B$15,0)),SMALL(IF(ISNA(MATCH(INDEX($A$16:$B$21,0,MATCH($B2,$A$15:$B$15,0)),$F$2:$F$13,0)),ROW(INDEX($A$16:$B$21,0,MATCH($B2,$A$15:$B$15,0)))-ROW($A$16)+1),COUNTIFS($B$2:$B2,$B2,$G$2:$G2,$G2)))))),""),"No Project selected"))

Cell J11 results in "Contractor". I was expecting to say "Mary" as she is someone from the team needed that would be free during that period of time for the amount of hours needed