Formula to match two columns and get a result from third one

[Kariba]

Hi, wondering if anyone can help? I'm trying to set up a table that will automatically calculate staff shift pay depending on when they start/finish. I've tried various formulas, but getting different results or none at all. Trying to get where both column C matches column J AND where column D matches column K, then put the value in from column L

Thanks

All dates, times, payments are made up in this example

Shift Pay.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Actual Clock In	Actual Clock Out	Rounded Clock In	Rounded Clock Out			Payment			Start	Finish	Payment
2	17/01/2024 05:36	17/01/2024 14:02	06:00	14:00	06:00	14:00	#N/A			06:00	14:00	5.43
3	18/01/2024 05:42	18/01/2024 14:00	06:00	14:00	06:00	14:00	#N/A			07:00	15:00	1.25
4	19/01/2024 07:42	19/01/2024 15:00	08:00	15:00	08:00	15:00	#N/A			08:00	15:00	2.53
5	20/01/2024 05:45	20/01/2024 14:00	06:00	14:00	06:00	14:00	#N/A
6
7
8
9
10
11
12
13
14
Sheet1

Cell Formulas
Range	Formula
C2:D5	C2	=TEXT(MROUND(A2,"1:00"),"hh:mm")
E2:F5	E2	=TEXT(C2,"hh:mm")
G2:G5	G2	=INDEX(L2:L10,MATCH(1,(J2:J10=C2)*(K2:K10=D2),0))

 

[shift-del]

Hello Kariba

Since there are no duplicates in colum J it is a simple XLOOKUP().

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Actual Clock In	Actual Clock Out	Rounded Clock In	Rounded Clock Out			Payment			Start	Finish	Payment
2	17/01/2024 05:36	17/01/2024 14:02	06:00:00	14:00:00			5.43			06:00:00	14:00:00	5.43
3	18/01/2024 05:42	18/01/2024 14:00	06:00:00	14:00:00			5.43			07:00:00	15:00:00	1.25
4	19/01/2024 07:42	19/01/2024 15:00	08:00:00	15:00:00			2.53			08:00:00	15:00:00	2.53
5	20/01/2024 05:45	20/01/2024 14:00	06:00:00	14:00:00			5.43
Data

Cell Formulas
Range	Formula
C2:D5	C2	=MROUND(MOD(A2,1),"1:00")
G2:G5	G2	=INDEX($L$2:$L$10,MATCH(1,($J$2:$J$10=C2)*($K$2:$K$10=D2),0))

 

[Kariba]

Hello Kariba

Since there are no duplicates in colum J it is a simple XLOOKUP().

Hello Kariba

Since there are no duplicates in colum J it is a simple XLOOKUP().

OK, will try that. There shouldn't be any duplicates in that table, but could be in the first 4 columns.

 

[Kariba]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Actual Clock In	Actual Clock Out	Rounded Clock In	Rounded Clock Out			Payment			Start	Finish	Payment
2	17/01/2024 05:36	17/01/2024 14:02	06:00:00	14:00:00			5.43			06:00:00	14:00:00	5.43
3	18/01/2024 05:42	18/01/2024 14:00	06:00:00	14:00:00			5.43			07:00:00	15:00:00	1.25
4	19/01/2024 07:42	19/01/2024 15:00	08:00:00	15:00:00			2.53			08:00:00	15:00:00	2.53
5	20/01/2024 05:45	20/01/2024 14:00	06:00:00	14:00:00			5.43
Data

Cell Formulas
Range	Formula
C2:D5	C2	=MROUND(MOD(A2,1),"1:00")
G2:G5	G2	=INDEX($L$2:$L$10,MATCH(1,($J$2:$J$10=C2)*($K$2:$K$10=D2),0))

Seems to work. I had tried MATCH, but suspected the issue might be in time format in C/D wasn't the same as J/K. Haven't used MOD before.

 

[Fluff]

You're welcome & thanks for the feedback.