INDEX / MATCH Used Horizontally & Looking for a Value Based on a Dropdown List

[LucieLiskova]

Hi All,

Second question this week, apologies. I have the below issue.

I have two Excel sheets Construction Payroll Hrs 2023 & Payroll File 2023. In Payroll File 2023, I need the Breakdown section (OT 1.5, OT 2, Holidays, Bank Holidays, Sick Paid, Sick Unpaid) to be filled out based on the week number in B5. This information will be taking from the Construction Payroll Hrs 2023. So number of let's say overtime 1.5 hours will match the Employee ID as well as the specific week i.e. in week 18, employee ID 1 will have the following hrs: 4.5 in OT 1.5, 10 in OT 2, 8 in bank holiday etc. These numbers will change depending on the week. I hope it makes sense.

Thank you.
Lucie.

Construction Payroll Hrs 2023.xlsx
	C	D	E							L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
4										PERIOD 10 wk18
5										Week 18
6										Mon		Tue		Wed		Thu		Fri		Sat		Sun															WEEK 18
7										01/05/2023		02/05/2023		03/05/2023		04/05/2023		05/05/2023		06/05/2023		07/05/2023
8	Employee ID	First Name	Surname							Shift	O/T	Shift	O/T	Shift	O/T	Shift	O/T	Shift	O/T	First 4 hrs	O/T	O/T	Annual Leave Total	Bank Holiday (10 days/year)	Sick Leave (3 days / 12-month period)	Sick Leave (day count)	Sick helpr formula	Annual Leave - half day	Annual Leave - full day	Parental Leave (unpaid)	Total Hrs Excl Sick and double OT	BASIC	OT 1.5	OT 1.5 HELPER	OT 2	OT 2 HELPER	TOTAL
9	1									BH	2	SL	1	8	1	8	1	7.5	1	5	2	3	0.00	8.00	8.00	1.00	8.00	0.00	0.00	0.00	42.50	31.50	4.50	11.00	10.00	5.00	54.00
10	2									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
11	3									BH	4	8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	43.50	39.50	6.00	4.00	0.00	0.00	45.50
12	4									BH	5	8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	44.50	39.50	7.50	5.00	0.00	0.00	47.00
13	5									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
14	6									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
15	7									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
16	8									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
17	9									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
18	10									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
19	11									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
20	12									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
21	13									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
22	14									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
23	15									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
24	16									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
25	17									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
26	18									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
27	19									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
28	20									BH		8		8		8		7.5					0.00	8.00	0.00	0.00	0.00	0.00	0.00	0.00	39.50	39.50	0.00	0.00	0.00	0.00	39.50
Construction - Payroll Hrs 2023

Cell Formulas
Range	Formula
Y9:Y28	Y9	=SUM(AD9:AE9)
Z9:Z28	Z9	=COUNTIF(L9:X9,"BH")*8
AA9:AA28	AA9	=IF(AB9<3,AB9*8,"")
AB9:AB28	AB9	=COUNTIF(L9:X9,"SL")
AC9:AC28	AC9	=COUNTIF(L9:X9,"SL")*8
AD9:AD28	AD9	=COUNTIF(L9:X9,"AL.5")*4
AE9:AE28	AE9	=COUNTIF(L9:X9,"AL")*8
AF9:AF28	AF9	=COUNTIF(L9:X9,"PL")*0
AG9:AG28	AG9	=SUM(L9:V9)+Y9+Z9
AH9:AH28	AH9	=SUM(L9,N9,P9,R9,T9,Y9,Z9)
AI9:AI28	AI9	=IF(AH9>39.5,(AH9+AJ9)*1.5,(AG9-39.5)*1.5)
AJ9:AJ28	AJ9	=SUM(M9,O9,Q9,S9,U9,V9)
AK9:AK28	AK9	=IF(AH9+AJ9>39.5,(W9+X9)*2,(W9+X9))
AL9:AL28	AL9	=SUM(W9:X9)
AM9:AM28	AM9	=IF(AH9>=39.5,AH9+AI9+AK9+AC9,(AH9+AC9+AI9+AK9))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R9:R28	Cell Value	contains "BH"	text	NO
R9:R28	Cell Value	contains "PL"	text	NO
R9:R28	Cell Value	contains "SL"	text	NO
R9:R28	Cell Value	contains "AL"	text	NO
P9:P28	Cell Value	contains "BH"	text	NO
P9:P28	Cell Value	contains "PL"	text	NO
P9:P28	Cell Value	contains "SL"	text	NO
P9:P28	Cell Value	contains "AL"	text	NO
V9:X28	Cell Value	contains "BH"	text	NO
V9:X28	Cell Value	contains "PL"	text	NO
V9:X28	Cell Value	contains "SL"	text	NO
V9:X28	Cell Value	contains "AL"	text	NO
T9:T28	Cell Value	contains "BH"	text	NO
T9:T28	Cell Value	contains "PL"	text	NO
T9:T28	Cell Value	contains "SL"	text	NO
T9:T28	Cell Value	contains "AL"	text	NO
N9:N28	Cell Value	contains "BH"	text	NO
N9:N28	Cell Value	contains "PL"	text	NO
N9:N28	Cell Value	contains "SL"	text	NO
N9:N28	Cell Value	contains "AL"	text	NO
L9:L28	Cell Value	contains "BH"	text	NO
L9:L28	Cell Value	contains "PL"	text	NO
L9:L28	Cell Value	contains "SL"	text	NO
L9:L28	Cell Value	contains "AL"	text	NO

Payroll File 2023..xlsx
	A	B	C			F	G	H	I	J	K	L
3	CONSTRUCTION PAYROLL
4	PERIOD	PERIOD 10 wk18
5	WEEK	Week 18
6
7		Employee ID	Total Hours			Breakdown
8						OT 1.5	OT 2	Holidays	Bank Holidays	Sick Paid	Sick Unpaid
9		1	54.00			0
10		2	39.50
11		3	45.50
12		4	47.00
13		5	39.50
14		6	39.50
15		7	39.50
16		8	39.50
17		9	39.50
18		10	39.50
19		11	39.50
20		12	39.50
21		13	39.50
22		14	39.50
23		15	39.50
24		16	39.50
25		17	39.50
26		18	39.50
27		19	39.50
Payroll File 2023

Cell Formulas
Range	Formula
B5	B5	=HLOOKUP(B$4,'C:\Users\lucie.liskova\Desktop\[Construction Payroll Hrs 2023 - Copy.xlsx]Construction - Payroll Hrs 2023'!$K$4:$FZ$28,2,FALSE)
F9	F9	=INDEX('C:\Users\lucie.liskova\Desktop\[Construction Payroll Hrs 2023 - Copy.xlsx]Construction - Payroll Hrs 2023'!$AI$9:$BK$9,MATCH(B5,'C:\Users\lucie.liskova\Desktop\[Construction Payroll Hrs 2023 - Copy.xlsx]Construction - Payroll Hrs 2023'!$AI$8:$BK$8))
C9:C10	C9	=INDEX('[Construction Payroll Hrs 2023-Copy]Construction - Payroll Hrs 2023'!$C$6:$AMJ$28,MATCH($B9,'[Construction Payroll Hrs 2023-Copy]Construction - Payroll Hrs 2023'!$C$6:$C$28,0),MATCH($B$5,'[Construction Payroll Hrs 2023-Copy]Construction - Payroll Hrs 2023'!$C$6:$AMJ$6,0))
C11:C27	C11	=INDEX('[Construction Payroll Hrs 2023-Copy]Construction - Payroll Hrs 2023'!$C$6:$AMJ$28,MATCH($B11,'[Construction Payroll Hrs 2023-Copy]Construction - Payroll Hrs 2023'!$C$6:$C$28,0),MATCH($B$5,'[Construction Payroll Hrs 2023-Copy]Construction - Payroll Hrs 2023'!$C$6:$AMJ$6,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
'Payroll File 2023'!_FilterDatabase	='Payroll File 2023'!$B$4:$B$4	B5

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=$AC$3:$AC$4

 

[LucieLiskova]

Hi All,

I tried this one: =VLOOKUP(B9,'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$C$9:$CQ$28,33,MATCH(B5,'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$L$5:$CQ$5,0)). However, it only works for the first value it needs to find, doesn't change afterwards.

Any ideas, please?

Thank you.


L.