IFNA VLOOKUP with multiple repeating table arrays with filter

[needhelp_please]

Hi all, I have an interesting issue I have been searching far and wide for a solution to no avail.

I have 3 employees who are assigned tasks every week. They rotate tasks every week. There are about 30 tasks, but they change every week so one week tasks A, J L K need to be assigned and the next week tasks B, C, E, F, K need to be assigned. They're always different.

I created a table with drop down lists of the different tasks. I was able to create a formula to randomly assign the tasks to the 3 employees, but they are not balanced. Some tasks are hard, others are not, so one employee should not have all hard tasks while another has none.

To randomly assign the tasks, I created 3 different lists randomly alternating the employee names. So I have 3 different vlookups that I use in the table:

Task A	Employee 1
Task B	Employee 2
Task C	Employee 3
Task D	Employee 1
Task E	Employee 2
Task F	Employee 3
Task G etc...	Employee 1 etc..

=IFNA(VLOOKUP(@F:F,Instructions!A12:B39, 2, FALSE), "")

Task A	Employee 2
Task B	Employee 3
Task C	Employee 1
Task D	Employee 2
Task E	Employee 3
Task F	Employee 1
Task G etc..	Employee 2 etc..

=IFNA(VLOOKUP(@F:F,Instructions!A42:B69, 2, FALSE), "")

Task A	Employee 3
Task B	Employee 1
Task C	Employee 2
Task D	Employee 3
Task E	Employee 1
Task F	Employee 2
Task G etc...	Employee 3 etc..

=IFNA(VLOOKUP(@F:F,Instructions!A72:B99, 2, FALSE), "")

I repeat these formulas by dragging them down the column in the table to assign the tasks.

In order to make it more balanced/fair, I decided to try alternating the employee names based on the difficulty of the different tasks:

Task A - easy	Employee 1
Task B - easy	Employee 1
Task C - medium	Employee 2
Task D - medium	Employee 2
Task E - hard	Employee 3
Task F - hard	Employee 3

Task A - easy	Employee 2
Task B - easy	Employee 2
Task C - medium	Employee 3
Task D - medium	Employee 3
Task E - hard	Employee 1
Task F - hard	Employee 1

Task A - easy	Employee 3
Task B - easy	Employee 3
Task C - medium	Employee 1
Task D - medium	Employee 1
Task E - hard	Employee 2
Task F - hard	Employee 2

Which made the assignments more balanced, but I still had to reassign a few tasks.

My problem is.. when I filtered the table and attempted to drag down the 3 repeating formulas, it only repeated the first formula. Apparently we cannot drag and drop alternating formulas down a filtered table. There are empty rows in the table so if I unfilter then drag down the formulas, the empty rows mess up the fairness/balance of the assigned tasks. So is there a way to combine these 3 formulas into one formula, so that I can drag down the one formula after filtering?

Here are the 3 formulas again:

=IFNA(VLOOKUP(@F:F,Instructions!A12:B39, 2, FALSE), "")
=IFNA(VLOOKUP(@F:F,Instructions!A42:B69, 2, FALSE), "")
=IFNA(VLOOKUP(@F:F,Instructions!A72:B99, 2, FALSE), "")

Essentially I would like a formula that assigns the first easy task to Employee 1, but the next time that task appears in the table it is assigned to Employee 2, etc. And does the same for the medium and hard tasks.

Let me know if I need to upload sheets to make this easier to understand.

There may be an easier way to assign tasks fairly altogether, I hope some geniuses out there can help me out!

 

[needhelp_please]

A few edits:

Some tasks are hard, others are not, so one employee should not have all hard tasks while another has no hard tasks and all easy ones.

Essentially I would like a formula that assigns the first easy task to Employee 1, but the next time an easy task appears in the table it is assigned to Employee 2, then the next time Employee 3.. then back to Employee 1, etc. And does the same for the medium and hard tasks.

Also just to clarify, I'm unable to delete the empty rows.

 

[needhelp_please]

Nvm I figured out how to combine the formulas into one using chatgpt

=IFNA(IF(MOD(ROW(),3)=1, VLOOKUP(@F:F,Instructions!$A$12:B$39, 2, FALSE), IF(MOD(ROW(),3)=2,VLOOKUP(@F:F,Instructions!$A$42:B$69, 2, FALSE),VLOOKUP(@F:F,Instructions!$A$72:B$99, 2, FALSE))),"")