Lookup PAAF ID based on Worker ID and date range

[alessiaexcels]

Hi there,
I am trying to populate column D in Sheet 'Hours by day and ID' with something called 'PAAF ID' - column A from Sheet 'PAAF IDs'. It needs to lookup based on Worker ID (on both sheets) and date. The date needs to be matched to date range of the relevant PAAF ID. I have included excel mini sheets for clarity. I am hoping to return the relevant PAAF ID in column D in Sheet 'Hours by day and ID' depending on what PAAF ID is effective for the date that there are hours booked. If the finish date is blank, it means it is still current.

Hope that makes sense and any help is much appreciated.
Thank you!

Kind regards
Alessia

Excel help_2.xlsx
	A	B	C	D
1	Worker ID	Day	Hours Booked	PAAF ID
2	100080	9/11/23	8
3	100080	9/12/23	8
4	100080	9/13/23	8
5	100080	9/14/23	8
6	100080	9/15/23	8
7	100080	9/18/23	8
8	100080	10/12/23	8
9	100080	10/13/23	8
10	100080	10/16/23	8
11	100080	10/17/23	8
12	100080	10/18/23	8
13	100080	10/19/23	8
14	100080	10/20/23	8
15	100080	10/23/23	8
16	100080	10/24/23	8
17	100080	10/25/23	8
18	100080	10/26/23	8
19	100080	10/27/23	8
20	100080	10/30/23	8
21	100080	10/31/23	8
22	100080	11/1/23	8
23	100080	11/2/23	8
24	100080	11/3/23	8
25	100080	11/6/23	8
26	100080	11/7/23	8
27	100080	11/8/23	8
28	100080	11/9/23	8
29	100080	11/10/23	8
30	100080	11/13/23	8
31	100080	11/14/23	8
32	100080	11/15/23	8
33	100080	11/21/23	8
34	100080	11/22/23	8
35	100080	11/23/23	8
36	100080	11/24/23	8
37	100080	11/27/23	8
38	100080	11/28/23	8
39	100080	11/29/23	8
40	100080	11/30/23	8
41	100080	12/1/23	8
42	100080	12/4/23	8
43	100080	12/5/23	8
44	100080	12/6/23	8
45	100080	12/7/23	8
46	100080	12/8/23	8
47	100080	12/11/23	8
48	100080	12/12/23	8
49	100080	12/13/23	8
50	100080	1/17/24	8
51	100080	1/18/24	8
52	100080	1/19/24	8
53	100080	1/22/24	8
54	100080	1/23/24	8
55	100080	1/25/24	8
56	100080	1/29/24	8
57	100080	1/30/24	8
58	100080	1/31/24	8
59	100080	2/1/24	8
60	100080	2/2/24	8
61	100080	2/5/24	8
62	100080	2/6/24	8
63	100080	2/7/24	8
64	100080	2/8/24	8
65	100080	2/12/24	8
66	100080	2/13/24	8
67	100080	2/14/24	8
68	100080	2/15/24	8
69	100080	2/16/24	8
70	100080	2/19/24	8
71	100080	2/20/24	8
72	100080	2/21/24	8
73	100080	2/22/24	8
74	100080	2/23/24	8
75	100080	2/26/24	8
76	100080	2/27/24	8
77	100080	2/28/24	8
78	100080	2/29/24	8
79	100080	3/1/24	8
80	100080	3/4/24	8
81	100080	3/5/24	8
82	100080	3/6/24	8
83	100080	3/7/24	8
84	100080	3/8/24	8
85	100080	3/11/24	8
86	100080	3/12/24	8
87	100080	3/13/24	8
88	100080	3/14/24	8
89	100080	3/15/24	8
90	100080	3/18/24	8
91	100080	4/4/24	8
92	100080	4/5/24	8
93	100080	4/8/24	8
94	100080	4/9/24	8
95	100080	4/10/24	8
96	100080	4/11/24	8
97	100080	4/12/24	8
98	100080	4/15/24	8
99	100080	4/16/24	8
100	100080	4/17/24	8
101	100080	4/18/24	8
102	100080	4/19/24	8
103	100080	4/22/24	8
104	100080	4/23/24	8
105	100080	4/24/24	8
106	100080	4/25/24	0
107	100080	4/26/24	8
108	100080	4/29/24	8
109	100080	4/30/24	8
110	100080	5/1/24	8
111	100080	5/2/24	8
112	100080	5/3/24	8
113	100080	5/4/24	10
114	100080	5/6/24	10
115	100080	5/7/24	10
116	100080	5/8/24	10
117	100080	5/9/24	10
118	100080	5/10/24	10
119	100080	5/11/24	10
120	100080	5/12/24	0
121	100080	5/13/24	10
122	100080	5/14/24	10
123	100080	5/15/24	10
124	100080	5/16/24	10
125	100080	5/17/24	0
126	100080	5/18/24	0
127	100080	5/19/24	0
128	100080	5/20/24	0
129	100080	5/21/24	0
130	100080	5/22/24	0
131	100080	5/23/24	0
132	100080	5/24/24	10
133	100080	5/25/24	10
134	100080	5/27/24	10
135	100080	5/28/24	10
136	100080	5/29/24	10
137	100080	5/30/24	10
138	100080	5/31/24	10
139	100080	6/1/24	10
140	100080	6/3/24	10
141	100080	6/4/24	10
142	100080	6/5/24	10
143	100080	6/6/24	10
144	100080	6/8/24	0
145	100080	6/10/24	0
146	100080	6/11/24	0
147	100080	6/12/24	0
148	100080	6/13/24	0
149	100080	6/14/24	10
150	100080	6/15/24	10
151	100080	6/17/24	10
152	100080	6/18/24	10
153	100080	7/17/24	0
154	100080	7/18/24	0
155	100080	7/19/24	10
156	100080	7/20/24	10
157	100080	7/22/24	10
158	100080	7/23/24	10
159	100080	7/24/24	10
160	100080	7/25/24	10
161	100080	7/26/24	10
162	100080	7/27/24	10
163	100080	7/29/24	10
164	100080	7/30/24	10
165	100080	7/31/24	10
166	100080	8/1/24	10
167	100080	8/2/24	0
168	100080	8/3/24	0
169	100080	8/5/24	0
170	100080	8/6/24	0
171	100080	8/7/24	0
172	100080	8/8/24	0
173	100080	8/9/24	10
174	100080	8/10/24	10
175	100080	8/12/24	10
176	100080	8/13/24	10
177	100080	8/14/24	10
178	100080	8/15/24	10
179	100080	8/16/24	10
180	100080	8/17/24	10
181	100080	8/18/24	0
182	100080	8/19/24	10
183	100080	8/20/24	10
184	100080	8/21/24	10
185	100080	8/22/24	10
186	100080	8/23/24	0
187	100080	8/24/24	0
188	100080	8/25/24	0
189	100080	8/26/24	0
190	100080	8/27/24	0
191	100080	8/28/24	0
192	100080	8/29/24	10
193	100080	8/30/24	10
194	100772	10/30/23	8
195	100772	10/31/23	8
196	100772	11/1/23	8
197	100772	11/2/23	8
198	100772	11/3/23	8
199	100772	11/20/23	8
200	100772	11/21/23	8
201	100772	11/22/23	8
202	100772	11/23/23	8
203	100772	11/24/23	8
204	100772	11/27/23	8
205	100772	11/28/23	8
206	100772	11/29/23	8
207	100772	11/30/23	8
208	100772	12/1/23	8
209	100772	12/4/23	8
210	100772	12/5/23	8
211	100772	12/6/23	8
212	100772	12/7/23	8
213	100772	12/8/23	8
214	100772	12/11/23	8
215	100772	12/12/23	8
216	100772	12/13/23	8
217	100772	12/14/23	8
218	100772	12/15/23	8
219	100772	12/18/23	8
220	100772	12/19/23	8
221	100772	12/20/23	8
222	100772	12/21/23	8
223	100772	12/22/23	8
224	100772	1/8/24	8
225	100772	1/9/24	8
226	100772	1/10/24	8
227	100772	1/11/24	8
228	100772	1/12/24	8
229	100772	1/15/24	8
230	100772	1/16/24	8
231	100772	1/17/24	8
232	100772	1/18/24	8
233	100772	1/19/24	8
234	100772	1/22/24	8
235	100772	1/23/24	8
236	100772	1/24/24	8
237	100772	1/25/24	8
238	100772	1/29/24	8
239	100772	1/30/24	8
240	100772	1/31/24	8
241	100772	2/1/24	8
242	100772	2/2/24	8
243	100772	2/5/24	8
244	100772	2/6/24	8
245	100772	2/7/24	8
246	100772	2/8/24	8
247	100772	2/9/24	8
248	100772	2/12/24	8
249	100772	2/13/24	8
250	100772	2/14/24	8
251	100772	2/15/24	8
252	100772	2/16/24	8
253	100772	2/19/24	8
254	100772	2/20/24	8
255	100772	2/21/24	8
256	100772	2/22/24	8
257	100772	2/23/24	8
258	100772	2/26/24	8
259	100772	2/27/24	8
260	100772	2/28/24	8
261	100772	2/29/24	8
262	100772	3/1/24	8
263	100772	3/4/24	0
264	100772	3/5/24	8
265	100772	3/6/24	8
266	100772	3/7/24	8
267	100772	3/8/24	8
268	100772	3/11/24	8
269	100772	3/12/24	8
270	100772	3/13/24	8
271	100772	3/14/24	8
272	100772	3/15/24	8
273	100772	3/18/24	8
274	100772	3/19/24	8
275	100772	3/20/24	8
276	100772	3/21/24	8
277	100772	3/22/24	8
278	100772	3/25/24	8
279	100772	3/26/24	8
280	100772	3/27/24	8
281	100772	3/28/24	8
282	100772	3/29/24	0
283	100772	4/1/24	0
284	100772	4/2/24	8
285	100772	4/3/24	8
286	100772	4/4/24	8
287	100772	4/5/24	8
288	100772	4/8/24	8
289	100772	4/9/24	8
290	100772	4/10/24	8
291	100772	4/11/24	8
292	100772	4/12/24	8
293	100772	4/15/24	8
294	100772	4/16/24	8
295	100772	4/17/24	8
296	100772	4/18/24	8
297	100772	4/19/24	8
298	100772	4/22/24	8
299	100772	4/23/24	8
300	100772	4/24/24	8
301	100772	4/29/24	8
302	100772	4/30/24	8
303	100772	5/1/24	8
304	100772	5/2/24	8
305	100772	5/3/24	8
306	100772	5/4/24	0
307	100772	5/5/24	0
308	100772	5/6/24	8
309	100772	5/7/24	8
310	100772	5/8/24	8
311	100772	5/9/24	8
312	100772	5/10/24	8
313	100772	5/13/24	8
314	100772	5/14/24	8
315	100772	5/15/24	8
316	100772	5/16/24	10
317	100772	7/8/24	10
318	100772	7/9/24	10
319	100772	7/10/24	10
320	100772	7/11/24	10
321	100772	7/12/24	10
322	100772	7/13/24	10
323	100772	7/15/24	10
324	100772	7/16/24	10
325	100772	7/17/24	10
326	100772	7/18/24	0
327	100772	7/18/24	0
328	100772	7/19/24	0
329	100772	7/19/24	0
330	100772	7/20/24	0
331	100772	7/21/24	0
332	100772	7/22/24	0
333	100772	7/23/24	0
334	100772	7/24/24	0
335	100772	7/25/24	0
336	100772	7/26/24	0
337	100772	7/27/24	0
338	100772	7/28/24	0
339	101751	9/13/23	2
340	101751	9/14/23	2
341	101751	9/15/23	2
342	101751	9/27/23	4
343	101751	9/28/23	4
344	101751	9/29/23	4
345	101751	10/4/23	4
346	101751	10/5/23	4
347	101751	10/6/23	4
348	101751	10/9/23	4
349	101751	10/10/23	4
350	101751	10/11/23	4
351	101751	10/12/23	4
352	101751	10/13/23	4
353	101751	10/23/23	2
354	101751	10/24/23	2
355	101751	10/25/23	2
356	101751	10/26/23	2
357	101751	10/27/23	2
358	102716	10/2/23	2
359	102716	10/3/23	2
360	102716	10/4/23	0
361	102716	10/5/23	0
362	102716	10/6/23	0
363	102716	10/9/23	2
364	102716	10/10/23	2
365	102716	10/16/23	2
366	102716	10/17/23	2
367	102716	10/18/23	2
368	102716	10/19/23	2
369	102716	10/20/23	2
370	102716	10/23/23	2
371	102716	10/24/23	2
372	102716	10/25/23	2
373	102716	10/26/23	2
374	102716	10/27/23	2
375	102716	10/30/23	2
376	102716	10/31/23	2
377	102716	11/1/23	2
378	102716	11/2/23	2
379	102716	11/3/23	2
380	102716	11/6/23	2
381	102716	11/7/23	2
382	102716	11/8/23	2
383	102716	11/9/23	2
384	102716	11/10/23	2
385	102716	11/13/23	2
386	102716	11/14/23	2
387	102716	11/15/23	2
388	102716	11/16/23	2
389	102716	11/17/23	2
390	102716	11/20/23	2
391	102716	11/21/23	2
392	102716	11/22/23	2
393	102716	11/23/23	2
394	102716	11/24/23	2
395	102716	11/27/23	2
396	102716	11/28/23	2
397	102716	11/29/23	2
398	102716	11/30/23	2
399	102716	12/1/23	2
400	102716	12/13/23	8
401	102716	12/15/23	2
402	102716	12/18/23	2
403	102716	12/19/23	2
404	102716	12/20/23	2
405	102716	12/21/23	2
406	102716	12/22/23	2
407	102716	1/10/24	8
408	102716	1/12/24	2
409	102716	1/17/24	8
410	102716	1/19/24	2
411	102716	1/24/24	8
412	102716	1/26/24	0
413	102716	1/31/24	8
414	102716	2/2/24	2
415	102716	2/7/24	8
416	102716	2/9/24	2
417	103716	9/11/23	0.5
418	103716	9/14/23	1
419	103716	9/18/23	1
420	103716	9/19/23	2.5
421	103716	9/20/23	1
422	103716	9/21/23	1
423	103716	9/22/23	1.5
424	103716	9/25/23	4
425	103716	9/26/23	2
426	103716	9/27/23	2
427	103716	9/28/23	3
428	103716	9/29/23	0.5
429	103716	10/3/23	2
430	103716	10/4/23	3
431	103716	10/9/23	2.5
432	103716	10/10/23	5
433	103716	10/11/23	2
434	103716	10/17/23	1.5
435	103716	10/18/23	2
436	103716	10/19/23	1
437	103716	10/24/23	1
438	103716	10/25/23	2
439	103716	10/26/23	1
440	103716	10/27/23	2
441	103716	10/30/23	1.5
442	103716	10/31/23	1
443	103716	11/1/23	2
444	103716	11/6/23	1
445	103716	11/7/23	1
446	103716	11/9/23	1
447	103716	11/10/23	1
448	103716	11/14/23	3
449	103716	11/15/23	1
450	103716	11/16/23	1
451	103716	11/17/23	4
452	103716	11/20/23	2.5
453	103716	11/21/23	2
454	103716	11/22/23	2
455	103716	11/23/23	2
456	103716	11/28/23	2
457	103716	11/29/23	3
458	103716	11/30/23	2
459	103716	12/4/23	1
460	103716	12/7/23	2
461	103716	12/8/23	1
462	103716	12/13/23	2
463	103716	1/12/24	2
464	103716	1/17/24	1
465	103716	4/5/24	2
466	103716	4/9/24	1.5
467	103716	4/10/24	3
468	103716	4/11/24	3
469	103716	7/8/24	2
470	103716	7/12/24	2
471	103854	10/3/23	8
472	103854	10/4/23	8
473	103854	10/5/23	8
474	103854	10/6/23	8
475	103854	10/9/23	8
476	103854	10/10/23	8
477	103854	10/11/23	5
478	103854	10/12/23	8
479	103854	10/13/23	8
480	103854	10/16/23	8
481	103854	10/17/23	8
482	103854	10/18/23	8
483	103854	10/19/23	8
484	103854	10/20/23	8
485	103854	10/23/23	8
486	103854	10/24/23	8
487	103854	10/25/23	8
488	103854	10/26/23	8
489	103854	10/27/23	8
490	103854	10/30/23	8
491	103854	10/31/23	8
492	103854	11/1/23	8
493	103854	11/2/23	8
494	103854	11/3/23	8
495	103854	11/6/23	3
496	103854	11/7/23	8
497	103854	11/8/23	8
498	103854	11/9/23	8
499	103854	11/10/23	8
500	103854	11/13/23	8
501	103854	11/14/23	8
502	103854	11/15/23	8
503	103854	11/16/23	8
504	103854	11/17/23	8
505	103854	11/20/23	8
506	103854	11/21/23	8
507	103854	11/22/23	8
508	103854	11/23/23	8
509	103854	11/24/23	8
510	103854	11/27/23	8
511	103854	11/28/23	8
512	103854	11/29/23	8
513	103854	11/30/23	8
514	103854	12/1/23	8
515	103854	12/5/23	4
516	103854	12/6/23	8
517	103854	12/7/23	8
518	103854	12/8/23	8
519	103854	12/11/23	8
520	103854	12/12/23	8
521	103854	12/13/23	8
522	103854	12/14/23	8
523	103854	12/15/23	8
524	103854	2/23/24	8
525	103854	2/26/24	8
526	103854	2/27/24	8
527	103854	2/28/24	8
528	103854	2/29/24	8
529	103854	3/1/24	8
530	103854	3/4/24	8
531	103854	3/5/24	8
532	103854	3/6/24	8
533	103854	3/7/24	8
534	103854	3/8/24	8
535	103854	3/11/24	8
536	103854	3/12/24	8
537	103854	3/13/24	8
538	103854	3/14/24	8
539	103854	3/15/24	8
540	103854	3/18/24	8
541	103854	3/19/24	8
542	103854	3/20/24	8
543	103854	3/21/24	8
544	103854	3/22/24	8
545	103854	3/25/24	8
546	103854	3/26/24	8
547	103854	3/27/24	8
548	103854	4/2/24	8
549	103854	4/3/24	8
550	103854	4/4/24	8
551	103854	4/5/24	8
552	103854	4/8/24	8
553	103854	4/9/24	8
554	103854	5/15/24	8
555	103854	5/16/24	8
556	103854	5/17/24	8
557	103854	5/20/24	8
558	103854	5/21/24	8
559	103854	5/22/24	8
560	103854	5/23/24	8
561	103854	5/24/24	8
562	103854	5/28/24	8
563	103854	5/29/24	8
564	103854	5/30/24	8
565	103854	5/31/24	8
566	103854	6/3/24	4
567	103854	6/4/24	8
568	103854	6/5/24	8
569	103854	6/6/24	8
570	103854	6/7/24	8
571	103854	6/10/24	8
572	103854	6/11/24	8
573	103854	6/12/24	8
574	103854	6/13/24	8
575	103854	6/14/24	8
576	103854	6/17/24	8
577	103854	6/18/24	8
578	103854	6/19/24	8
579	103854	6/20/24	8
580	103854	6/21/24	8
581	103854	6/24/24	0
582	103854	6/25/24	8
583	103854	6/26/24	8
584	103854	6/27/24	8
585	103854	6/28/24	8
586	103854	7/1/24	7.6
587	103854	7/2/24	7.6
588	103854	7/3/24	7.6
589	103854	7/4/24	7.6
590	103854	7/5/24	7.6
591	103854	7/8/24	7.6
592	103854	7/9/24	7.6
593	103854	7/10/24	7.6
594	103854	7/11/24	7.6
595	103854	7/12/24	6.6
596	103854	7/15/24	5
597	103854	7/16/24	7.6
598	103854	7/17/24	7.6
599	103854	7/18/24	7.6
600	103854	7/19/24	7.6
601	103854	7/22/24	7.6
602	103854	7/23/24	7.6
603	103854	7/24/24	7.6
604	103854	7/25/24	7.6
605	103854	7/26/24	7.6
606	103854	7/29/24	7.6
607	103854	7/30/24	7.6
608	103854	7/31/24	7.6
609	103854	8/1/24	7.6
610	103854	8/2/24	7.6
611	103854	8/5/24	7.6
612	103854	8/6/24	7.6
613	103854	8/7/24	7.6
614	103854	8/8/24	7.6
615	103854	8/9/24	7.6
616	103854	8/12/24	7.6
617	103854	8/13/24	7.6
618	103854	8/15/24	7.6
619	103854	8/16/24	7.6
620	103854	8/19/24	7.6
Hours by day and ID

Excel help_2.xlsx
	A	B	C	D
1	PAAF ID	Worker ID	Start Date	Finish Date
2	1.00	103854	9/11/23	6/30/24
3	1.01	103854	7/1/24
4	2.00	102716	9/13/23	2/15/24
5	3.00	103716	9/11/23	9/30/23
6	3.01	103716	10/1/23	7/12/24
7	4.00	101751	9/11/23	9/30/23
8	4.01	101751	10/1/23	10/27/23
9	5.00	100080	9/11/23	9/30/23
10	5.01	100080	10/1/23	4/3/24
11	5.02	100080	4/4/24	6/30/24
12	5.03	100080	7/1/24	8/11/24
13	5.04	100080	8/12/24
14	6.00	100772	9/11/23	9/30/23
15	6.01	100772	10/1/23	1/31/24
16	6.02	100772	2/1/24	5/15/24
17	6.03	100772	5/16/24	6/30/24
18	6.04	100772	7/1/24	8/11/24
19	6.05	100772	8/12/24
PAAF IDs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO
A:A	Cell Value	duplicates	text	NO
A:A	Cell Value	duplicates	text	NO

 

[Fluff]

How about

=TAKE(FILTER('PAAF IDs'!$A$2:$A$100,('PAAF IDs'!$B$2:$B$100=A2)*('PAAF IDs'!$C$2:$C$100<=B2)),-1)