Allocating the right resource to the machine running

[Cotchy]

We have 3 machines (Line 1,Line 2,Line 3) available where only 2 at any given time operate over our 5 days work week.I have 2 workers for day shift and 2 workers for night shift that I need to automatically allocate their name against the machines that are rostered to be run. One worker per machine. The 4 workers have a set roster where they work every day. If Line 1 is not running then the 2 day shift workers need to be rostered on Lines 2 and 3. Same for night shift. If Line 2 is not scheduled to run then the 2 day shift workers need to be automatically allocated to Lines 1 and 3. The other variable is that if a worker was rostered on a certain machine the previous day then the following day they would be rostered onto a different machine.

Anyone have any suggestions on how I can do this using 2 tables. Table 1 showing a list of dates that machines are rostered to run and a separate table showing the normal work hours for the workers.

Any help appreciated.

[ChemistB]

Can you attach an example spreadsheet showing what you want the final tables to look like? (See yellow banner at top of page). This would help enormously with us creating formulas to do the task.