improve xlookup speed (and add date comparison)

[whexcelitious]

I constructed an XLOOKUP to find the right rate of a worker from a set of contracts. The rate needs to be added to a list of timesheet records that hold the name of the worker, the contract he worked for, etc. So when every timesheet-row has the right rate, I can construct a pivot that shows the total hours worked and the total value (hours x rate) of that contract in a month-based overview.
However, I struggle finding the right rate... And my current solution (does not include the contract period yet..) is already quite slow (the total list of timesheet records counts about 8000 entries).
Q1: how can I expand the xlookup with taking the contract period into account
Q2: what would be a faster query to get the same result?

contractRate.xlsx
	A	B	C	D	E	F
1	workOrder	workerName	contractStart	contractEnd	Rate	Timecode
2	w11111	John Doe	1-1-2022	30-6-2022	61	A
3	w11111	John Doe	1-1-2022	30-6-2022	66	P
4	w11111	John Doe	1-1-2022	31-12-2022	71	A
5	w11111	John Doe	1-1-2022	31-12-2022	76	P
6	w22222	John Doe	1-1-2022	30-6-2022	62	A
7	w22222	John Doe	1-1-2022	30-6-2022	67	P
8	w22222	John Doe	1-1-2022	31-12-2022	72	A
9	w22222	John Doe	1-1-2022	31-12-2022	77	P
10	w22222	Michael Foe	1-1-2022	30-6-2022	82	A
11	w22222	Michael Foe	1-1-2022	30-6-2022	87	P
12	w22222	Michael Foe	1-1-2022	31-12-2022	92	A
13	w22222	Michael Foe	1-1-2022	31-12-2022	97	P
Contracts

This is the timesheet list: the red rate cells show the wrong rate due to the fact that the wrong contract is found.

contractRate.xlsx
	A	B	C	D	E	F
1	date	Timecode	workerName	workOrder	#hours	rate?
2	1-2-2022	A	John Doe	w11111	1	61
3	1-2-2022	P	John Doe	w11111	1	66
4	1-7-2022	A	John Doe	w11111	1	61
5	1-7-2022	P	John Doe	w11111	1	66
6	1-2-2022	A	John Doe	w22222	1	62
7	1-2-2022	P	John Doe	w22222	1	67
8	1-7-2022	A	John Doe	w22222	1	62
9	1-7-2022	P	John Doe	w22222	1	67
10	1-2-2022	A	Michael Foe	w22222	1	82
11	1-2-2022	P	Michael Foe	w22222	1	87
12	1-7-2022	A	Michael Foe	w22222	1	82
13	1-7-2022	P	Michael Foe	w22222	1	87
Timesheet

Cell Formulas
Range	Formula
F2:F13	F2	=XLOOKUP(1,(Contracts!F:F=Timesheet!B2)*(Contracts!B:B=Timesheet!C2)*(Contracts!A:A=Timesheet!D2),Contracts!E:E)

Named Ranges
Name	Refers To	Cells
Contracts!_FilterDatabase	=Contracts!$A$1:$F$5	F2:F13

any suggestions?

 

[Fluff]

Hi & welcome to MrExcel.

1st thing is never use whole column references, that is why everything is so slow.
2nd if row 4 on the time sheet had a date in March which rate should be returned 61 or 71?

 

[whexcelitious]

Hi & welcome to MrExcel.

1st thing is never use whole column references, that is why everything is so slow.
2nd if row 4 on the time sheet had a date in March which rate should be returned 61 or 71?

Thnx Fluff,

To answer the last question first: both values are valid according to the contract, so either answer is ok. The second answer (71) is slightly better because that contract was created later (not visible in this dataset) so a contract with a later enddate replaces a contract that ends earlier.

Your first suggestion is not clear to me: how can I search the entire table of contract without using column references?

 

[Fluff]

You use column references, but not the entire column.
How about

=TAKE(FILTER(Contracts!$E$2:$E$10000,(Contracts!$F$2:$F$10000=B2)*(Contracts!$B$2:$B$10000=C2)*(Contracts!$A$2:$A$10000=D2)*(Contracts!$D$2:$D$10000>=A2)),-1)

 

[whexcelitious]

ah, never knew that A:A and A1:A1000 are different in terms of performance.
You use the TAKE() function which is a new function in O365, right? Although my Excel says it is a 365 version (Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20646) 64-bit ) it does not know the TAKE function.
Is there another way to choose the last member of the returned array?

 

[Fluff]

You are behind on your updates, try

=LET(f,FILTER(Contracts!$E$2:$E$10000,(Contracts!$F$2:$F$10000=B2)*(Contracts!$B$2:$B$10000=C2)*(Contracts!$A$2:$A$10000=D2)*(Contracts!$D$2:$D$10000>=A2)),INDEX(f, ROWS(f)))

 

[whexcelitious]

You are behind on your updates, try

=LET(f,FILTER(Contracts!$E$2:$E$10000,(Contracts!$F$2:$F$10000=B2)*(Contracts!$B$2:$B$10000=C2)*(Contracts!$A$2:$A$10000=D2)*(Contracts!$D$2:$D$10000>=A2)),INDEX(f, ROWS(f)))

updates are managed by IT department (department of "no"...) so I will keep a lookout for TAKE() function but use your latest proposal instead.
Thanx a lot for that! I even managed to improve it a bit because your code only takes the end date of the contract into account. If e.g. the contracts do not overlap (say: the second contract of John Doe starts after the work date in the timesheet) your code still takes the second contract as valid. In this code the startdate is also added as filter:

=LET(f;FILTER(Contracts!$E$2:$E$100;(Contracts!$F$2:$F$100=B2)*(Contracts!$B$2:$B$100=C2)*(Contracts!$A$2:$A$100=D2)*(Contracts!$D$2:$D$100>=A2)*(Contracts!$C$2:$C$100<=A2));INDEX(f; ROWS(f)))

again: thnx a lot for your fast replies and helping me to understand Excel even better!

 

[Fluff]

Glad to help & thanks for the feedback.