Lookup PAAF ID based on Worker ID and date range

alessiaexcels

New Member
Joined
Jul 29, 2024
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1Worker IDDayHours BookedPAAF ID
21000809/11/238
31000809/12/238
41000809/13/238
51000809/14/238
61000809/15/238
71000809/18/238
810008010/12/238
910008010/13/238
1010008010/16/238
1110008010/17/238
1210008010/18/238
1310008010/19/238
1410008010/20/238
1510008010/23/238
1610008010/24/238
1710008010/25/238
1810008010/26/238
1910008010/27/238
2010008010/30/238
2110008010/31/238
2210008011/1/238
2310008011/2/238
2410008011/3/238
2510008011/6/238
2610008011/7/238
2710008011/8/238
2810008011/9/238
2910008011/10/238
3010008011/13/238
3110008011/14/238
3210008011/15/238
3310008011/21/238
3410008011/22/238
3510008011/23/238
3610008011/24/238
3710008011/27/238
3810008011/28/238
3910008011/29/238
4010008011/30/238
4110008012/1/238
4210008012/4/238
4310008012/5/238
4410008012/6/238
4510008012/7/238
4610008012/8/238
4710008012/11/238
4810008012/12/238
4910008012/13/238
501000801/17/248
511000801/18/248
521000801/19/248
531000801/22/248
541000801/23/248
551000801/25/248
561000801/29/248
571000801/30/248
581000801/31/248
591000802/1/248
601000802/2/248
611000802/5/248
621000802/6/248
631000802/7/248
641000802/8/248
651000802/12/248
661000802/13/248
671000802/14/248
681000802/15/248
691000802/16/248
701000802/19/248
711000802/20/248
721000802/21/248
731000802/22/248
741000802/23/248
751000802/26/248
761000802/27/248
771000802/28/248
781000802/29/248
791000803/1/248
801000803/4/248
811000803/5/248
821000803/6/248
831000803/7/248
841000803/8/248
851000803/11/248
861000803/12/248
871000803/13/248
881000803/14/248
891000803/15/248
901000803/18/248
911000804/4/248
921000804/5/248
931000804/8/248
941000804/9/248
951000804/10/248
961000804/11/248
971000804/12/248
981000804/15/248
991000804/16/248
1001000804/17/248
1011000804/18/248
1021000804/19/248
1031000804/22/248
1041000804/23/248
1051000804/24/248
1061000804/25/240
1071000804/26/248
1081000804/29/248
1091000804/30/248
1101000805/1/248
1111000805/2/248
1121000805/3/248
1131000805/4/2410
1141000805/6/2410
1151000805/7/2410
1161000805/8/2410
1171000805/9/2410
1181000805/10/2410
1191000805/11/2410
1201000805/12/240
1211000805/13/2410
1221000805/14/2410
1231000805/15/2410
1241000805/16/2410
1251000805/17/240
1261000805/18/240
1271000805/19/240
1281000805/20/240
1291000805/21/240
1301000805/22/240
1311000805/23/240
1321000805/24/2410
1331000805/25/2410
1341000805/27/2410
1351000805/28/2410
1361000805/29/2410
1371000805/30/2410
1381000805/31/2410
1391000806/1/2410
1401000806/3/2410
1411000806/4/2410
1421000806/5/2410
1431000806/6/2410
1441000806/8/240
1451000806/10/240
1461000806/11/240
1471000806/12/240
1481000806/13/240
1491000806/14/2410
1501000806/15/2410
1511000806/17/2410
1521000806/18/2410
1531000807/17/240
1541000807/18/240
1551000807/19/2410
1561000807/20/2410
1571000807/22/2410
1581000807/23/2410
1591000807/24/2410
1601000807/25/2410
1611000807/26/2410
1621000807/27/2410
1631000807/29/2410
1641000807/30/2410
1651000807/31/2410
1661000808/1/2410
1671000808/2/240
1681000808/3/240
1691000808/5/240
1701000808/6/240
1711000808/7/240
1721000808/8/240
1731000808/9/2410
1741000808/10/2410
1751000808/12/2410
1761000808/13/2410
1771000808/14/2410
1781000808/15/2410
1791000808/16/2410
1801000808/17/2410
1811000808/18/240
1821000808/19/2410
1831000808/20/2410
1841000808/21/2410
1851000808/22/2410
1861000808/23/240
1871000808/24/240
1881000808/25/240
1891000808/26/240
1901000808/27/240
1911000808/28/240
1921000808/29/2410
1931000808/30/2410
19410077210/30/238
19510077210/31/238
19610077211/1/238
19710077211/2/238
19810077211/3/238
19910077211/20/238
20010077211/21/238
20110077211/22/238
20210077211/23/238
20310077211/24/238
20410077211/27/238
20510077211/28/238
20610077211/29/238
20710077211/30/238
20810077212/1/238
20910077212/4/238
21010077212/5/238
21110077212/6/238
21210077212/7/238
21310077212/8/238
21410077212/11/238
21510077212/12/238
21610077212/13/238
21710077212/14/238
21810077212/15/238
21910077212/18/238
22010077212/19/238
22110077212/20/238
22210077212/21/238
22310077212/22/238
2241007721/8/248
2251007721/9/248
2261007721/10/248
2271007721/11/248
2281007721/12/248
2291007721/15/248
2301007721/16/248
2311007721/17/248
2321007721/18/248
2331007721/19/248
2341007721/22/248
2351007721/23/248
2361007721/24/248
2371007721/25/248
2381007721/29/248
2391007721/30/248
2401007721/31/248
2411007722/1/248
2421007722/2/248
2431007722/5/248
2441007722/6/248
2451007722/7/248
2461007722/8/248
2471007722/9/248
2481007722/12/248
2491007722/13/248
2501007722/14/248
2511007722/15/248
2521007722/16/248
2531007722/19/248
2541007722/20/248
2551007722/21/248
2561007722/22/248
2571007722/23/248
2581007722/26/248
2591007722/27/248
2601007722/28/248
2611007722/29/248
2621007723/1/248
2631007723/4/240
2641007723/5/248
2651007723/6/248
2661007723/7/248
2671007723/8/248
2681007723/11/248
2691007723/12/248
2701007723/13/248
2711007723/14/248
2721007723/15/248
2731007723/18/248
2741007723/19/248
2751007723/20/248
2761007723/21/248
2771007723/22/248
2781007723/25/248
2791007723/26/248
2801007723/27/248
2811007723/28/248
2821007723/29/240
2831007724/1/240
2841007724/2/248
2851007724/3/248
2861007724/4/248
2871007724/5/248
2881007724/8/248
2891007724/9/248
2901007724/10/248
2911007724/11/248
2921007724/12/248
2931007724/15/248
2941007724/16/248
2951007724/17/248
2961007724/18/248
2971007724/19/248
2981007724/22/248
2991007724/23/248
3001007724/24/248
3011007724/29/248
3021007724/30/248
3031007725/1/248
3041007725/2/248
3051007725/3/248
3061007725/4/240
3071007725/5/240
3081007725/6/248
3091007725/7/248
3101007725/8/248
3111007725/9/248
3121007725/10/248
3131007725/13/248
3141007725/14/248
3151007725/15/248
3161007725/16/2410
3171007727/8/2410
3181007727/9/2410
3191007727/10/2410
3201007727/11/2410
3211007727/12/2410
3221007727/13/2410
3231007727/15/2410
3241007727/16/2410
3251007727/17/2410
3261007727/18/240
3271007727/18/240
3281007727/19/240
3291007727/19/240
3301007727/20/240
3311007727/21/240
3321007727/22/240
3331007727/23/240
3341007727/24/240
3351007727/25/240
3361007727/26/240
3371007727/27/240
3381007727/28/240
3391017519/13/232
3401017519/14/232
3411017519/15/232
3421017519/27/234
3431017519/28/234
3441017519/29/234
34510175110/4/234
34610175110/5/234
34710175110/6/234
34810175110/9/234
34910175110/10/234
35010175110/11/234
35110175110/12/234
35210175110/13/234
35310175110/23/232
35410175110/24/232
35510175110/25/232
35610175110/26/232
35710175110/27/232
35810271610/2/232
35910271610/3/232
36010271610/4/230
36110271610/5/230
36210271610/6/230
36310271610/9/232
36410271610/10/232
36510271610/16/232
36610271610/17/232
36710271610/18/232
36810271610/19/232
36910271610/20/232
37010271610/23/232
37110271610/24/232
37210271610/25/232
37310271610/26/232
37410271610/27/232
37510271610/30/232
37610271610/31/232
37710271611/1/232
37810271611/2/232
37910271611/3/232
38010271611/6/232
38110271611/7/232
38210271611/8/232
38310271611/9/232
38410271611/10/232
38510271611/13/232
38610271611/14/232
38710271611/15/232
38810271611/16/232
38910271611/17/232
39010271611/20/232
39110271611/21/232
39210271611/22/232
39310271611/23/232
39410271611/24/232
39510271611/27/232
39610271611/28/232
39710271611/29/232
39810271611/30/232
39910271612/1/232
40010271612/13/238
40110271612/15/232
40210271612/18/232
40310271612/19/232
40410271612/20/232
40510271612/21/232
40610271612/22/232
4071027161/10/248
4081027161/12/242
4091027161/17/248
4101027161/19/242
4111027161/24/248
4121027161/26/240
4131027161/31/248
4141027162/2/242
4151027162/7/248
4161027162/9/242
4171037169/11/230.5
4181037169/14/231
4191037169/18/231
4201037169/19/232.5
4211037169/20/231
4221037169/21/231
4231037169/22/231.5
4241037169/25/234
4251037169/26/232
4261037169/27/232
4271037169/28/233
4281037169/29/230.5
42910371610/3/232
43010371610/4/233
43110371610/9/232.5
43210371610/10/235
43310371610/11/232
43410371610/17/231.5
43510371610/18/232
43610371610/19/231
43710371610/24/231
43810371610/25/232
43910371610/26/231
44010371610/27/232
44110371610/30/231.5
44210371610/31/231
44310371611/1/232
44410371611/6/231
44510371611/7/231
44610371611/9/231
44710371611/10/231
44810371611/14/233
44910371611/15/231
45010371611/16/231
45110371611/17/234
45210371611/20/232.5
45310371611/21/232
45410371611/22/232
45510371611/23/232
45610371611/28/232
45710371611/29/233
45810371611/30/232
45910371612/4/231
46010371612/7/232
46110371612/8/231
46210371612/13/232
4631037161/12/242
4641037161/17/241
4651037164/5/242
4661037164/9/241.5
4671037164/10/243
4681037164/11/243
4691037167/8/242
4701037167/12/242
47110385410/3/238
47210385410/4/238
47310385410/5/238
47410385410/6/238
47510385410/9/238
47610385410/10/238
47710385410/11/235
47810385410/12/238
47910385410/13/238
48010385410/16/238
48110385410/17/238
48210385410/18/238
48310385410/19/238
48410385410/20/238
48510385410/23/238
48610385410/24/238
48710385410/25/238
48810385410/26/238
48910385410/27/238
49010385410/30/238
49110385410/31/238
49210385411/1/238
49310385411/2/238
49410385411/3/238
49510385411/6/233
49610385411/7/238
49710385411/8/238
49810385411/9/238
49910385411/10/238
50010385411/13/238
50110385411/14/238
50210385411/15/238
50310385411/16/238
50410385411/17/238
50510385411/20/238
50610385411/21/238
50710385411/22/238
50810385411/23/238
50910385411/24/238
51010385411/27/238
51110385411/28/238
51210385411/29/238
51310385411/30/238
51410385412/1/238
51510385412/5/234
51610385412/6/238
51710385412/7/238
51810385412/8/238
51910385412/11/238
52010385412/12/238
52110385412/13/238
52210385412/14/238
52310385412/15/238
5241038542/23/248
5251038542/26/248
5261038542/27/248
5271038542/28/248
5281038542/29/248
5291038543/1/248
5301038543/4/248
5311038543/5/248
5321038543/6/248
5331038543/7/248
5341038543/8/248
5351038543/11/248
5361038543/12/248
5371038543/13/248
5381038543/14/248
5391038543/15/248
5401038543/18/248
5411038543/19/248
5421038543/20/248
5431038543/21/248
5441038543/22/248
5451038543/25/248
5461038543/26/248
5471038543/27/248
5481038544/2/248
5491038544/3/248
5501038544/4/248
5511038544/5/248
5521038544/8/248
5531038544/9/248
5541038545/15/248
5551038545/16/248
5561038545/17/248
5571038545/20/248
5581038545/21/248
5591038545/22/248
5601038545/23/248
5611038545/24/248
5621038545/28/248
5631038545/29/248
5641038545/30/248
5651038545/31/248
5661038546/3/244
5671038546/4/248
5681038546/5/248
5691038546/6/248
5701038546/7/248
5711038546/10/248
5721038546/11/248
5731038546/12/248
5741038546/13/248
5751038546/14/248
5761038546/17/248
5771038546/18/248
5781038546/19/248
5791038546/20/248
5801038546/21/248
5811038546/24/240
5821038546/25/248
5831038546/26/248
5841038546/27/248
5851038546/28/248
5861038547/1/247.6
5871038547/2/247.6
5881038547/3/247.6
5891038547/4/247.6
5901038547/5/247.6
5911038547/8/247.6
5921038547/9/247.6
5931038547/10/247.6
5941038547/11/247.6
5951038547/12/246.6
5961038547/15/245
5971038547/16/247.6
5981038547/17/247.6
5991038547/18/247.6
6001038547/19/247.6
6011038547/22/247.6
6021038547/23/247.6
6031038547/24/247.6
6041038547/25/247.6
6051038547/26/247.6
6061038547/29/247.6
6071038547/30/247.6
6081038547/31/247.6
6091038548/1/247.6
6101038548/2/247.6
6111038548/5/247.6
6121038548/6/247.6
6131038548/7/247.6
6141038548/8/247.6
6151038548/9/247.6
6161038548/12/247.6
6171038548/13/247.6
6181038548/15/247.6
6191038548/16/247.6
6201038548/19/247.6
Hours by day and ID



Excel help_2.xlsx
ABCD
1 PAAF ID Worker IDStart Date Finish Date
21.001038549/11/236/30/24
31.011038547/1/24
42.001027169/13/232/15/24
53.001037169/11/239/30/23
63.0110371610/1/237/12/24
74.001017519/11/239/30/23
84.0110175110/1/2310/27/23
95.001000809/11/239/30/23
105.0110008010/1/234/3/24
115.021000804/4/246/30/24
125.031000807/1/248/11/24
135.041000808/12/24
146.001007729/11/239/30/23
156.0110077210/1/231/31/24
166.021007722/1/245/15/24
176.031007725/16/246/30/24
186.041007727/1/248/11/24
196.051007728/12/24
PAAF IDs
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACell ValueduplicatestextNO
A:ACell ValueduplicatestextNO
A:ACell ValueduplicatestextNO
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
How about
Excel Formula:
=TAKE(FILTER('PAAF IDs'!$A$2:$A$100,('PAAF IDs'!$B$2:$B$100=A2)*('PAAF IDs'!$C$2:$C$100<=B2)),-1)
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top