Mark Adjacent Column if Before or After Reference Date

[paulfitz320]

Hi,

Stuck with this, would appreciate some help please.

I need a formula for COLUMN I

Start at COL D (Horse) then go across to...
COL H - If it contains a 1 for that horse leave adjacent cell in COL I blank
COL H - If it contains a 2 for that horse put a 4 in COL I (If the date in COL B is AFTER the date where the 1 occured in COL H for that horse)
COL H - If it contains a 2 for that horse put a 3 in COL I (If the date in COL B is BEFORE the date where the 1 occured in COL H for that horse)

wpm hurdles won hurdles debut sp less then 9to2.xlsx
	A	B	C	D	E	F	G	H	I
1	Course	Date	Time	Horse	Fin Pos	Hurdle Runs Before	Race Type	hurdle debut yn	After 1 Next Door
2	Punchestown	27/04/2021	17:55	Adamantly Chosen	1	0	NHF	2	3
3	Fairyhouse	27/11/2021	13:01	Adamantly Chosen	2	0	Hurdle	1
4	Cork	08/01/2022	13:03	Adamantly Chosen	2	1	Hurdle	2	4
5	Thurles	10/02/2022	14:55	Adamantly Chosen	1	2	Hurdle	2	4
6	Cheltenham	18/03/2022	17:30	Adamantly Chosen	11	3	Hurdle	2	4
7	Punchestown	30/04/2022	17:30	Adamantly Chosen	4	4	Hurdle	2	4
8	Galway	28/07/2022	17:05	Adamantly Chosen	13	5	Hurdle	2	4
9	Listowel	19/09/2022	17:05	Adamantly Chosen	1	6	Chase	2	4
10	Punchestown	12/10/2022	15:55	Adamantly Chosen	1	6	Chase	2	4
11	Fairyhouse	04/12/2022	13:55	Adamantly Chosen	5	6	Chase	2	4
12	Limerick	26/12/2022	14:40	Adamantly Chosen	2	6	Chase	2	4
13	Leopardstown	05/02/2023	13:40	Adamantly Chosen	2	6	Chase	2	4
14	Cheltenham	15/03/2023	14:10	Adamantly Chosen	7	6	Chase	2	4
15	Fairyhouse	09/04/2023	16:55	Adamantly Chosen	6	6	Chase	2	4
16	Punchestown	28/04/2023	16:15	Adamantly Chosen	PU	6	Chase	2	4
17	Killarney	19/08/2022	16:05	Aghaboy	2	0	Hurdle	1
18	Ballinrobe	09/09/2022	15:50	Aghaboy	1	1	Hurdle	2	4
19	Listowel	18/09/2022	14:10	Aghaboy	1	2	Hurdle	2	4
20	Fairyhouse	04/12/2022	12:25	Aghaboy	7	3	Hurdle	2	4
21	Leopardstown	26/12/2022	13:10	Aghaboy	7	4	Hurdle	2	4
22	Galway	03/08/2023	17:40	Aghaboy	13	5	Turf	2	4
Sheet1

 

[paulfitz320]

Thanks very much to you both. Thats working fine now.

 

[Peter_SSs]

You’re welcome. Thanks for the follow-up.