I am trying to build a dynamic filter that will filter a data range based on an list of employee #’s AND the pay period they started and stopped working on a project (I feel like this should be easy, but I can’t figure it out!). The dataset I’m pulling from has data for the whole company for the year. I only need a subset of the employees (easy enough to filter) but then I want to filter that based on when they started with the group or when they left the group. I need the formula to be dynamic to be able to handle additions to the employee list, which could be up to 80 employees. Its almost like I need to filter an array by an array, if that’s possible?!

Filter List:
Employee # Employee Name From Pay Period To Pay Period
1000 DOE, JANE 3 10
2100 ADAMS, BRIAN 1 27
2500 PEPPERS, JULIE 7 27
3000 LONGS, PIPI 1 27
3600 COLLINS, JOHN 1 10
4000 CONNOR, CINDY 1 27
4700 LIST, PATTY 1 27

I have been able to somewhat accomplish what I am trying to do with stacked filters (below), but but I could have up to 80 lines and it doesn’t seem reasonable (and I don’t even think possible) to stack 80 filters to handle that potential (in addition, I get errors when the line is blank so I can’t over add filters for future data). I’ve looked into Query options but can’t figure out how to pass the individual criteria for each line into the query.

I’m sure there is a better way that I am missing. I’ve searched for a few days and nothing seems to handle this problem. I am open to a formulaic solution or a scripts solution (I’ve thought about incorporating a loop into a filter in scripts, but that got over my head).

This is what “works” with a small set of data:
={Filter(‘Raw Data’!A:K,’Raw Data’!A:A=A2,’Raw Data’!K:K >=C2, ‘Raw Data’!K:K<=D2);
Filter(‘Raw Data’!A:K,’Raw Data’!A:A=A3,’Raw Data’!K:K >=C3, ‘Raw Data’!K:K <=D3);
Filter(‘Raw Data’!A:K,’Raw Data’!A:A=A4,’Raw Data’!K:K >=C4, ‘Raw Data’!K:K <=D4);
Filter(‘Raw Data’!A:K,’Raw Data’!A:A=A5,’Raw Data’!K:K >=C5, ‘Raw Data’!K:K <=D5);
Filter(‘Raw Data’!A:K,’Raw Data’!A:A=A6,’Raw Data’!K:K >=C6, ‘Raw Data’!K:K <=D6);
Filter(‘Raw Data’!A:K,’Raw Data’!A:A=A7,’Raw Data’!K:K >=C7, ‘Raw Data’!K:K <=D7);
Filter(‘Raw Data’!A:K,’Raw Data’!A:A=A8,’Raw Data’!K:K >=C8, ‘Raw Data’!K:K <=D8)}

Here is a link to my raw data and the filter. The results in G-Q are what I’m wanting based on above formula, but not sure how to do this with many lines of employees. So looking for a better more powerful approach.
https://docs.google.com/spreadsheets/d/15AzXOp34Tw-DTQtYewyHzBCybqJcs2U1k7HFXLaG-cY/edit#gid=0