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.

 

[Tikey]

Hi, is it permissible for me to ask you a question about your reply to my question yesterday, something is happening I don't understand, or should I start a new post ?

 

[Fluff]

Hi, is it permissible for me to ask you a question about your reply to my question yesterday,

Yes that's fine?

 

[Tikey]

The sample sheet I enclosed was of course very small. In reality it contains several 000 rows, sometimes I use most of these, sometimes only a few, but the sheet is pre- configured for both eventualities.

Your suggestion works great for all months except January. If there is no data for months Feb to Dec and if there are several rows not containing any data the response is #CALC!, which is fine, as I can wrap the formula with iferror and return a blank cell, which I do as the answers are used in other formulas. But, if there is no data for Jan and several rows not containing any data, it returns 00/01/1900, which to the other formulas looks good, but it affects the answer.

I have attached a small sheet with the formulas configured up to row 100 which hopefully shows what I mean.

The formula is as you suggested =INDEX(SORT(FILTER($A$3:$B$100,MONTH($A$3:$A$100)=1),2,-1),1,1) for Jan, the same for the others other than a change to the month number.

Date	Value		Max Value Date
			Jan	Feb	Mar	Apr	May	June
01/03/2021​	1​		00/01/1900	#CALC!	03/03/2021	06/04/2021	09/05/2021	#CALC!
02/03/2021​	2​
03/03/2021​	3​
04/04/2021​	1​
05/04/2021​	2​
06/04/2021​	4​
07/05/2021​	2​
08/05/2021​	3​
09/05/2021​	4​

 

[Fluff]

Ok, how about

=INDEX(SORT(FILTER($A$3:$B$100,(MONTH($A$3:$A$100)=1)*($A$3:$A$100<>""),{"",""}),2,-1),1,1)

The blanks cells are effectively 0 which the formula reads a January.

 

[Tikey]

That works great thank you. Do you know why it was only January that was affected ?

 

[Fluff]

Any blank cell is treated as 0 which (date wise) Excel treats Jan

 

[Tikey]

Thanks again.

 

[Fluff]

My pleasure.