Find job code based on 2 criteria in a different data set no longer working

[cmacnab]

Hi, I have 2 data sets. I need to find the job code based on a date & employee ID. The formula was working with the prior data set but now I have changed one data set and the formula is no longer working. The logic is fairly simple, on a specific date, for a specific person, what job were they working?

The 1st data set that works (column Q is the result I'm looking for):

PP01-2022 Reuben's (2020-12-13 to 2021-12-26).xlsx
	A	B	C					H	I		K	L	M			P	Q
1	ADP ID#	CashoutDescription	dateof					netsales	totalcc		totalcctips	employeename	employeeid			displayname	Job Code
2	7CN6200000072	American Express	Tue-14-Dec-21					$ 32.00	$ 42.31		$ 5.52	Andres R	406			Reubens	SERVER
3	7CN6200000072	Cash	Tue-14-Dec-21					$ 272.50	$ -		$ -	Andres R	406			Reubens	SERVER
4	7CN6200000072	Interact	Tue-14-Dec-21					$ 233.00	$ 309.75		$ 41.87	Andres R	406			Reubens	SERVER
5	7CN6200000072	Master Charge	Tue-14-Dec-21					$ 153.00	$ 201.77		$ 25.86	Andres R	406			Reubens	SERVER
6	7CN6200000072	VISA	Tue-14-Dec-21					$ 322.50	$ 428.90		$ 58.11	Andres R	406			Reubens	SERVER
7	7CN6200000072	Cash	Thu-16-Dec-21					$ 102.00	$ -		$ -	Andres R	406			Reubens	BARTENDER
8	7CN6200000072	Interact	Thu-16-Dec-21					$ 36.00	$ 47.60		$ 6.21	Andres R	406			Reubens	BARTENDER
9	7CN6200000072	Master Charge	Thu-16-Dec-21					$ 30.00	$ 39.66		$ 5.17	Andres R	406			Reubens	BARTENDER
10	7CN6200000072	American Express	Sat-18-Dec-21					$ 143.00	$ 197.29		$ 32.88	Andres R	406			Reubens	SERVER
11	7CN6200000072	Cash	Sat-18-Dec-21					$ 203.00	$ -		$ -	Andres R	406			Reubens	SERVER
12	7CN6200000072	Interact	Sat-18-Dec-21					$ 209.50	$ 277.82		$ 36.94	Andres R	406			Reubens	SERVER
13	7CN6200000072	Master Charge	Sat-18-Dec-21					$ 505.79	$ 684.66		$ 103.13	Andres R	406			Reubens	SERVER
14	7CN6200000072	VISA	Sat-18-Dec-21					$ 360.50	$ 480.96		$ 66.46	Andres R	406			Reubens	SERVER
15	7CN6200000072	American Express	Sun-19-Dec-21					$ 35.00	$ 46.28		$ 6.04	Andres R	406			Reubens	BARTENDER
16	7CN6200000072	Master Charge	Sun-19-Dec-21					$ 138.50	$ 183.14		$ 23.89	Andres R	406			Reubens	BARTENDER
Declared Tips & Pool

Cell Formulas
Range	Formula
A2:A16	A2	=VLOOKUP($M2,'Validation Sheet'!$D:$F,3,FALSE)
Q2:Q16	Q2	=IFERROR(INDEX('Punch Report'!$H:$H,MATCH(1,($C2='Punch Report'!$B:$B)*($A2='Punch Report'!$A:$A),0)),"Takeout")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
'Punch Report'!_FilterDatabase	='Punch Report'!$A$1:$U$338	Q2:Q16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q2:Q482	Cell Value	="Takeout"	text	NO
Q2:Q482	Cell Value	="BARTENDER"	text	NO
Q2:Q482	Cell Value	="SERVER"	text	NO
Q1,Q483:Q1048576	Cell Value	="Takeout"	text	NO
Q1,Q483:Q1048576	Cell Value	="BARTENDER"	text	NO
Q1,Q483:Q1048576	Cell Value	="SERVER"	text	NO

This is the data set that it is looking at for the result:

PP01-2022 Reuben's (2020-12-13 to 2021-12-26).xlsx
	A	B				F	G	H	I			L	M		O			R
1	ADP ID#	Single				Invoice	Paid	Opened	Closed			Employee Name	Employee ID		AMPM			Job Code
34	7CN6200000072	1				2921743	36.79	Tue-14-Dec-21	Tue-14-Dec-21			Andres R	406		AM			#N/A
35	7CN6200000072	1				2921767	55.19	Tue-14-Dec-21	Tue-14-Dec-21			Andres R	406		AM			#N/A
36	7CN6200000072	1				2921779	27.59	Tue-14-Dec-21	Tue-14-Dec-21			Andres R	406		AM			#N/A
37	7CN6200000072	1				2921778	27.59	Tue-14-Dec-21	Tue-14-Dec-21			Andres R	406		AM			#N/A
38	7CN6200000072	1				2921777	25.29	Tue-14-Dec-21	Tue-14-Dec-21			Andres R	406		AM			#N/A
Sales by Shift PP01-2022

Cell Formulas
Range	Formula
A34:A38	A34	=VLOOKUP($M34,'Validation Sheet'!$D:$F,3,FALSE)
R34:R38	R34	=INDEX('Punch Report'!$H:$H,MATCH(1,($H34='Punch Report'!$B:$B)*($A34='Punch Report'!$A:$A),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
'Punch Report'!_FilterDatabase	='Punch Report'!$A$1:$U$338	R34:R38

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R2:R2240	Cell Value	="Takeout"	text	NO
R2:R2240	Cell Value	="BARTENDER"	text	NO
R2:R2240	Cell Value	="SERVER"	text	NO
R1	Cell Value	="Takeout"	text	NO
R1	Cell Value	="BARTENDER"	text	NO
R1	Cell Value	="SERVER"	text	NO

Any help is greatly appreciated!

 

[Fluff]

Try

{=INDEX('Punch Report'!$H:$H,MATCH(1,(INT($H34)='Punch Report'!$B:$B)*($A34='Punch Report'!$A:$A),0))}

 

[cmacnab]

Try

{=INDEX('Punch Report'!$H:$H,MATCH(1,(INT($H34)='Punch Report'!$B:$B)*($A34='Punch Report'!$A:$A),0))}

Thanks Fluff, unfortunately didn't work. Got #NAME? as a result. See below (R2 is an array formula, not sure why it is not showing it):

PP01-2022 Reuben's (2020-12-13 to 2021-12-26).xlsx
	A	B				F	G	H	I			L	M		O			R
1	ADP ID#	Single				Invoice	Paid	Opened	Closed			Employee Name	Employee ID		AMPM			Job Code
2	7CN6000000050	1				2921751	96.01	Tue-14-Dec-21	Tue-14-Dec-21			Danny C	302		AM			#NAME?
3	7CN6000000050	1				2921762	64.39	Tue-14-Dec-21	Tue-14-Dec-21			Danny C	302		AM			#NAME?
4	7CN6000000050	1				2921772	88.53	Tue-14-Dec-21	Tue-14-Dec-21			Danny C	302		AM			#NAME?
5	7CN6000000050	1				2921814	100.03	Tue-14-Dec-21	Tue-14-Dec-21			Danny C	302		AM			#NAME?
Sales by Shift PP01-2022

Cell Formulas
Range	Formula
A2:A5	A2	=VLOOKUP($M2,'Validation Sheet'!$D:$F,3,FALSE)
R2	R2	=INDEX('Punch Report'!$H:$H,MATCH(INT,($H2='Punch Report'!$B:$B)*($A2='Punch Report'!$A:$A),0))
R3:R5	R3	=INDEX('Punch Report'!$H:$H,MATCH(INT,($H3='Punch Report'!$B:$B)*($A3='Punch Report'!$A:$A),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
'Punch Report'!_FilterDatabase	='Punch Report'!$A$1:$U$338	R2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R2:R2240	Cell Value	="Takeout"	text	NO
R2:R2240	Cell Value	="BARTENDER"	text	NO
R2:R2240	Cell Value	="SERVER"	text	NO
R1	Cell Value	="Takeout"	text	NO
R1	Cell Value	="BARTENDER"	text	NO
R1	Cell Value	="SERVER"	text	NO

 

[Fluff]

That is not what I suggested.