Return Value in Adjacent Cell

[pilot330]

Hi

I need a formula for COL F please.

COL E returns the max value in COL C for each horse.

In COL F, I need to get the value in the cell adjacent (COL D)

If there is more than 1 max value for a horse (See Chocolate Box) then I the need the higher adjacent value in COL D returned (78 in this case.

Thanks

Book2
	A	B	C	D	E	F
1	Date	Horse	Spd Rat	OR	Max Spd Rat Last 12 months	Adjacent OR
2	05/04/2021	Al Kout	81	80	99	0
3	17/03/2021	Al Kout	90	80	99
4	02/03/2021	Al Kout	66	80	99
5	11/11/2020	Al Kout	91	76	99
6	18/10/2020	Al Kout	99	0	88
7	07/10/2020	Al Kout	85	76	88
8	21/09/2020	Al Kout	79	79	88
9	21/08/2020	Al Kout	3	75	88
10	18/07/2020	Al Kout	55	78	88
11	27/06/2020	Al Kout	85	80	88
12	09/06/2020	Al Kout	88	82	0
13	05/04/2021	Cadeau D'Or	71	82	89	0
14	28/10/2020	Cadeau D'Or	81	82	89
15	02/10/2020	Cadeau D'Or	88	79	89
16	11/09/2020	Cadeau D'Or	89	0	0
17	22/04/2021	Chocolate Box	81	78	84	78
18	06/03/2021	Chocolate Box	84	78	84
19	16/02/2021	Chocolate Box	84	76	79
20	01/02/2021	Chocolate Box	79	78	0
21	13/04/2021	Clongowes	97	100	99	100
22	04/04/2021	Clongowes	99	100	94
23	04/03/2021	Clongowes	92	102	94
24	04/02/2021	Clongowes	50	104	94
25	13/01/2021	Clongowes	94	0	83
26	07/12/2020	Clongowes	83	0	62
27	08/11/2020	Clongowes	62	0	0
Sheet1

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
How about

=MAXIFS($D$2:$D$100,$B$2:$B$100,B2,$C$2:$C$100,MAXIFS($C$2:$C$100,$B$2:$B$100,B2))

 

[pilot330]

Thanks Fluff,

I tried that. The formula is returning the max value for the first row of each horse right throughout the entire range of values for each horse.

For example when Al Kout's value in E6 changes to 88, the formula needs to look down COL C until it finds 88 for that horse, and then return the adjacent value, '82' in this case.

Here is what a working formula should return in COL F

Thank you.

Book2
	A	B	C	D	E	F
1	Date	Horse	Spd Rat	OR	Max Spd Rat Last 12 months	Adjacent OR
2	05/04/2021	Al Kout	81	80	99	0
3	17/03/2021	Al Kout	90	80	99	0
4	02/03/2021	Al Kout	66	80	99	0
5	11/11/2020	Al Kout	91	76	99	0
6	18/10/2020	Al Kout	99	0	88	82
7	07/10/2020	Al Kout	85	76	88	82
8	21/09/2020	Al Kout	79	79	88	82
9	21/08/2020	Al Kout	3	75	88	82
10	18/07/2020	Al Kout	55	78	88	82
11	27/06/2020	Al Kout	85	80	88	82
12	09/06/2020	Al Kout	88	82	0	0
13	05/04/2021	Cadeau D'Or	71	82	89	0
14	28/10/2020	Cadeau D'Or	81	82	89	0
15	02/10/2020	Cadeau D'Or	88	79	89	0
16	11/09/2020	Cadeau D'Or	89	0	0	0
17	22/04/2021	Chocolate Box	81	78	84	78
18	06/03/2021	Chocolate Box	84	78	84	76
19	16/02/2021	Chocolate Box	84	76	79	78
20	01/02/2021	Chocolate Box	79	78	0	0
21	13/04/2021	Clongowes	97	100	99	100
22	04/04/2021	Clongowes	99	100	94	0
23	04/03/2021	Clongowes	92	102	94	0
24	04/02/2021	Clongowes	50	104	94	0
25	13/01/2021	Clongowes	94	0	83	0
26	07/12/2020	Clongowes	83	0	62	0
27	08/11/2020	Clongowes	62	0	0	0
Sheet1

 

[Fluff]

That is not what you showed in your op.
Also please update your profile to show your version of Excel as requested.
Finally why is F18 76 and not 78?

 

[pilot330]

Apologies for the lack of clarity.
The formula needs to lookdown. So if the formula is in row 18 (F18) then it needs to lookdown COL C starting at row 19 (for that horse). Hence the 76 in relation to your query.
I will update my profile.
Thanks, I understand this is awkward.

 

[Fluff]

Originally you said you needed the value from col D adjacent to the max value in col C & if there are two max values in C get the highest from col D.
Are you now saying that all that is irrelevant?

 

[pilot330]

No it's relevant. But the max value returned needs to be from an earlier date, hence the formula needs to look for that max value starting from the row below (for that horse).

 

[Fluff]

Ok how about

=MAXIFS($D3:$D$100,$B3:$B$100,B2,$C3:$C$100,E2)

 

[pilot330]

Okay that's perfect now.

Thanks very much.

 

[Fluff]

You're welcome & thanks for the feedback.