In Excel 2021 what is the formula to return a date when the max value happened (Compare the same month over many years)

[LRATOZ]

I am looking for an Excel formula that returns the date when the highest value happened for a particular month.

I have weather data going back over six years. All the data is in the same table on the "Data" tab. Column B (Starting from B3) has the date in the following format: 31-01-2020
(The dates start at 15-11-2015 and goes until today 07-09-2023)

Maybe this could be useful: I also got Column C which shows the year and column D which shows the month (Both derived from the date).
I created these columns to experiment but it would be nice if I could delete them once I got a working formula.

Column K (Starting from K3) has all max temperature values for each date.

To put it simply: I want to find out what the date was when the maximum temperature occurred during the month of January over all these years.
If there are months of January with the same max value then select the oldest date. Empty cells in column K should be ignored.

Obviously, I want to expand it for every month of the year but once I get the concept to work it out for January then I can do the same for the other months as well.
This has me puzzled for many months. I am using a formula as follows:

=INDEX(Data!$B$3:$B$2855,MATCH(D5,Data!$K$3:$K$2855,0),1)

However, this works accurately for about 50% of the time. The problem is that if a max value is the same for different months then it might pick a date that correlates to a different month.

I've done some reading and it seems that the MAXIFS would be a more suitable formula.
Can somebody please put me on the right way?
Thank you very much in advance!
Luke

 

[Tetra201]

Assuming you have your month of interest in cell C5 (e.g., 1 for January), try:

=XLOOKUP(MAXIFS(Data!$K$3:$K$2855,Data!$D$3:$D$2855,C5),FILTER(Data!$K$3:$K$2855,Data!$D$3:$D$2855=C5),FILTER(Data!$B$3:$B$2855,Data!$D$3:$D$2855=C5))

 

[LRATOZ]

Hi Tetra201,

That suggestion is most helpful. However, due to my lack of knowledge, I'm not there yet.
It must be pretty hard for you especially when you don't have the spreadsheet available to you.
So, I basically got two tabs in the spreadsheet.
The first tab is called "Summary".
See the attached screenshot of that page.

Then the tab where the raw data is stored is called "Data".
See the second screenshot.

In case you can't read it: The headers for the "Data"-sheet are:
B2 = Date
C2 = Year
D2 = Month
...
K2 = AWS temp Max

I added the Year and Month column for experimentation but it would be great if I could just get rid of it. The only purpose it's there was for easy filtering of data (But that didn't work anyway).

So, basically all the dates, starting from 01-01-2016 until today (and expanding by one row each day), are in the B-column
The values are in Column K. (Later on I want to expand to the other columns as well, but, if I get one working formula then it will be easy to modify)
I want to see the maximum value appearing on the Summary page in cell B6 and the month reference is taken from B4.

So, I want to have a lookup function for the maximum temperature for the month of January over all these years.
Does that make sense to you?
I feel you are on the right track. It just needs a little tweak to get it right.
I really appreciate your time and effort trying to help me out!
Cheers,

Luke