Date of Max value

[Tikey]

Hi

I’m using Office pro plus 2021.

I am trying to find the date of the maximum number, when 2 conditions are true, the year and a code. The following is an example sheet, in reality the sheet could contain 50000 rows of data and the columns may not be adjacent.

This sometimes can appear to work OK, but depending on the combination of date / number, can sometimes return a date for the wrong year.

Code	Date	Number
A	02/06/1995​	1​
b	03/06/1995​	7​
b	04/06/1995​	9​
A	05/06/1995​	1​
A	06/06/1995​	2​
b	07/06/1995​	6​
A	08/06/1995​	1​
A	09/08/1996​	1​
A	10/08/1996​	1​
b	11/08/1996​	12​
b	12/08/1996​	10​
A	13/08/1996​	1​
A	14/08/1996​	2​
A	15/08/1996​	11​
A	16/08/1996​	5​
A	24/07/2001​	9​
A	25/07/2001​	5​
A	26/07/2001​	2​
A	27/07/2001​	4​
A	28/07/2001​	1​
A	29/07/2001​	1​

=INDEX(C2:C22,MATCH(MAX(IF((B2:B22="A")*(YEAR(C2:C22)=2001),D2:D22)),D2:D22,0))

In the above I would expect 24/07/2001 to be the answer, but it returns 04/06/1995, which is the wrong year and code.

Any help appreciated.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Code	Date	Number
2	A	02/06/1995	1		24/07/2001
3	b	03/06/1995	7
4	b	04/06/1995	9
5	A	05/06/1995	1
6	A	06/06/1995	2
7	b	07/06/1995	6
8	A	08/06/1995	1
9	A	09/08/1996	1
10	A	10/08/1996	1
11	b	11/08/1996	12
12	b	12/08/1996	10
13	A	13/08/1996	1
14	A	14/08/1996	2
15	A	15/08/1996	11
16	A	16/08/1996	5
17	A	24/07/2001	9
18	A	25/07/2001	5
19	A	26/07/2001	2
20	A	27/07/2001	4
21	A	28/07/2001	1
22	A	29/07/2001	1
Data

Cell Formulas
Range	Formula
E2	E2	=INDEX(SORT(FILTER(B2:C100,(A2:A100="A")*(YEAR(B2:B100)=2001)),2,-1),1,1)

 

[Tikey]

Thank you that works fine for the example, but as I indicated in the post, the columns may not be adjacent and in this situation it doesn't work. Not sure if columns B and C could be seperated, rather than B2:C100 ?

 

[Fluff]

Try

=INDEX(SORT(FILTER(CHOOSE({1,2},B2:B100,C2:C100),(A2:A100="A")*(YEAR(B2:B100)=2001)),2,-1),1,1)

 

[Tikey]

Thank you, that works great, much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.