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

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

Thank you

My apologies. My explanation was not very clear.

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

"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.
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
"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

Hi Ali,

Sorry about this. I created the other post because I haven't received any replies on this forum. I tried to share the link but I get this message:

Errors
The following errors occurred with your submission
You are not allowed to post any kinds of links, images or videos until you post a few times.

It's post on mrexcel and reddit as well

htt ps: //ww w.red dit.co m/r/excel/comments/p6mxsa/value_from_list_depending_on_conditions_that/
htt ps: //ww w.mrex cel.co m/board/thread...ished.1179422/