Index / Match.... maybe

[shaztastic73]

Hi,

I'm not sure if Index / Match is the right way forward here as I seem to be going round in circles a bit, any help much appreciated....

I have the below table with a calculated column [Exclude.Items]
If an "Exclude" appears in the [Exclude.Items] column I need to copy it to the cells highlighted in Yellow as the values for [Employee.Reference] and [Updates.ProcessDate] match.....

2021-07-09 - Holiday pay xxxxxxx.xlsx
	B	C	D			G											R	S	T
1	Employees.Reference	Employees.WorksNumber	Employees.Surname			Updates.ProcessDate											PaymentHistory.PaymentAmtCur	Exclude.Items	Column1
2	7	007	Johnstone			10/05/2019											10
3	7	007	Johnstone			10/05/2019											895.65
4	7	007	Johnstone			16/05/2019											10
5	7	007	Johnstone			16/05/2019											895.65
6	7	007	Johnstone			23/05/2019											10
7	7	007	Johnstone			23/05/2019											895.65
8	7	007	Johnstone			31/05/2019											895.65
9	7	007	Johnstone			31/05/2019											10
10	7	007	Johnstone			06/06/2019											10
11	7	007	Johnstone			06/06/2019											895.65
12	7	007	Johnstone			13/06/2019											179.13
13	7	007	Johnstone			13/06/2019											10
14	7	007	Johnstone			13/06/2019											716.52	EXCLUDE
15	7	007	Johnstone			20/06/2019											895.65
16	7	007	Johnstone			20/06/2019											10
17	7	007	Johnstone			27/06/2019											10
18	7	007	Johnstone			27/06/2019											895.65
19	7	007	Johnstone			04/07/2019											10
20	7	007	Johnstone			04/07/2019											895.65
21	7	007	Johnstone			11/07/2019											895.65
22	7	007	Johnstone			11/07/2019											10
23	7	007	Johnstone			18/07/2019											10
24	7	007	Johnstone			18/07/2019											895.65
SageReportData1

Cell Formulas
Range	Formula
S2:S24	S2	=IFERROR(IFS(COUNTIF([@[PaymentHistory.PaymentDescription]],"*HOLIDAY*"),"EXCLUDE",COUNTIF([@[PaymentHistory.PaymentDescription]],"*SICK*"),"EXCLUDE",COUNTIF([@[PaymentHistory.PaymentDescription]],"*FURL*"),"EXCLUDE",COUNTIF([@[Updates.SSPPaid]],">0"),"EXCLUDE",COUNTIF([@[Updates.SPPPaid]],">0"),"EXCLUDE",COUNTIF([@[Updates.SMPPaid]],">0"),"EXCLUDE",COUNTIF([@[Updates.SSPPaid]],">0"),"EXCLUDE",COUNTIF([@[Updates.SAPPaid]],">0"),"EXCLUDE",COUNTIF([@[Updates.ASPPPaid]],">0"),"EXCLUDE"),"")

 

[jasonb75]

I think that the easiest way would be

=IF(COUNTIFS([Employee.Reference],[@[Employee.Reference]],[Updates.ProcessDate],[@[Updates.ProcessDate]],[Exclude.Items],"EXCLUDE"),"EXCLUDE","")

 

[shaztastic73]

I think that the easiest way would be

=IF(COUNTIFS([Employee.Reference],[@[Employee.Reference]],[Updates.ProcessDate],[@[Updates.ProcessDate]],[Exclude.Items],"EXCLUDE"),"EXCLUDE","")

That's worked a treat, thanks so much