Google Sheets, multi-tab, multi-criteria if statements

I'm building a staff allocation sheet for our production teams so that management can see (graphically) the peaks and troughs for each department. I've anonymised the data and shared it here: https://docs.google.com/spreadsheets/d/140w_v_ApksXH2q7h_dK5Iglm0VOTF1Zk9fq9jPxF5BM/edit?usp=sharing

What I am attempting to achieve is to have the week commencing dates across the top, the employees down the left (Based on a unique list of employees form the department tabs) and the individual cells populated with the relevant show number (I've manually entered the first two employees).

I had done this successfully by having hidden proxy columns for each show that pull a start and end date from the department sheet via and indirect lookup. Where this falls down is if an employee works on a production at two separate times, e.g. all of May and all of August but not between those months.

I initially attempted a series of nested ifs:

=if(and(indirect($A3&"!$B:$B")=$B:$B, 
indirect($A3&"!$E:$E")<=AK$2, 
indirect($A3&"!$F:$F")>AK$2,
indirect($A3&"!$C:$C")="SHOW #1"
),1,
if(and(indirect($A3&"!$B:$B")=$B:$B, 
indirect($A3&"!$E:$E")<=AK$2, 
indirect($A3&"!$F:$F")>AK$2,
indirect($A3&"!$C:$C")="SHOW #2"
),2,
if(and(indirect($A3&"!$B:$B")=$B:$B, 
indirect($A3&"!$E:$E")<=AK$2, 
indirect($A3&"!$F:$F")>AK$2,
indirect($A3&"!$C:$C")="SHOW #3"
),3,
. . . 
if(and(indirect($A3&"!$B:$B")=$B:$B, 
indirect($A3&"!$E:$E")<=AK$2, 
indirect($A3&"!$F:$F")>AK$2,
indirect($A3&"!$C:$C")="SHOW #17"
),17,
0)))))))))))))))))

Hoping that this would calculate in each cell and return the correct result, however this did not work, that's when I discovered if() doesn't work with ranges in this context! I've effectively hit a wall on this.

I feel like I'm missing something obvious in this and would appreciate any help!!!