Date of associated max value

[Tikey]

I have a range of dates and associated data. I would like to find the date of the maximum value for each month, the following simple table hopefully shows this:

07/01/1992​	1​
08/01/1992​	5​
09/01/1992​	1​
10/01/1992​	1​
07/02/1992​	1​
08/02/1992​	1​
09/02/1992​	2​
10/02/1992​	1​
20/03/1992​	1​
21/03/1992​	1​
22/03/1992​	4​
23/03/1992​	3​
01/04/1992​	1​
02/04/1992​	1​
03/04/1992​	4​
04/04/1992​	1​

So, assuming I wanted the date of the max value for March, the answer I require is 22/03/1992.

Can anyone offer a solution. Thank you.

 

[Fluff]

How about

+Fluff 1.xlsm
	I	J	K	L	M
1
2	01/07/1992	1		01/03/1992	22/03/1992
3	01/08/1992	5
4	01/09/1992	1
5	01/10/1992	1
6	02/07/1992	1
7	02/08/1992	1
8	02/09/1992	2
9	02/10/1992	1
10	20/03/1992	1
11	21/03/1992	1
12	22/03/1992	4
13	23/03/1992	3
14	04/01/1992	1
15	04/02/1992	1
16	04/03/1992	4
17	04/04/1992	1
18
Primary

Cell Formulas
Range	Formula
M2	M2	=INDEX(SORT(FILTER(I2:J17,TEXT(I2:I17,"mmmyyyy")=TEXT(L2,"mmmyyyy")),2,-1),1,1)

 

[Tikey]

That certainly works, thank you. Is there a way of doing it without having to add the date in cell L2 ?

 

[Fluff]

Yup, like

=INDEX(SORT(FILTER(I2:J17,TEXT(I2:I17,"mmmyyyy")="Mar1992"),2,-1),1,1)

 

[Tikey]

Taking this a stage further. As I am only interested in the month and the formula will be used over a number of different years and I don't wish to have to edit the formula to suit the year, is there a way of doing that ?

 

[Fluff]

Why didn't you say that last time?
Are there any other changes you want to make?

 

[Tikey]

I'm sorry, no more changes. The question did't come to me until I saw your answer.

 

[Fluff]

Ok, how about

=INDEX(SORT(FILTER(I2:J17,MONTH(I2:I17)=3),2,-1),1,1)

 

[Tikey]

That works great, thank you very much. Apologies that I caused you more work.

 

[Fluff]

You're welcome & thanks for the feedback.