INDEX MATCH show the latest date where identical values match

[LRATOZ]

Hi,
I have a list of weather data where I have thousands of row.
The first column shows the date and a second column shows a value (Let's say temperature)
I want to find out on which date the maximum value happened.

I am using a formula like this:
=INDEX(A40:A54,MATCH(D40,B40:B54,0),1)

Here is a practical and small example without any useful content (Just for demonstration purposes:

Date	Value		Find		Result
1/01/2021​	37​		85​		7/01/2021​
2/01/2021​	25​
3/01/2021​	68​
4/01/2021​	25​
5/01/2021​	63​
6/01/2021​	54​
7/01/2021​	85​
8/01/2021​	16​
9/01/2021​	28​
10/01/2021​	95​
11/01/2021​	15​
12/01/2021​	46​
13/01/2021​	85​
14/01/2021​	24​
15/01/2021​	69​

Now, as you can see, I have two values of "85" in the Value-column but the INDEX MATCH formula lists the first date that this occurred.
My preference would be to see the latest date when this value occurred. So, in the "Result"-cell I would like to see "13-01-21" instead of "07-01-2021"; Is this feasible by using the INDEX MATCH function?
I would like to prefer to see a solution using the INDEX MATCH function as I got a basic understanding of this function.
Many thanks in advance!
Cheers,

Luke

 

[Saba Sabaratnam]

Hi Luke,

You could use the following array formula to get the result that you want.

=MAX(IF($B$2:$B$16=$E$2,$A$2:$A$16,0))

Enter the above formula in F2 by pressing CONTROL + SHIFT + ENTER keys

Kind regards

Saba

 

[LRATOZ]

Hi Saba,

Great answer! Thank you very much for that.
It does exactly what I asked but now I'm experiencing a different problem.

Here's an excerpt from my spreadsheet
The top row contains the months and the maximum value recorded for each month over the years.
The second row contains the maximum value ever measured for each month
The third row show the matching dates in which I applied my INDEX MATCH formula as shown in B3
The fourth row shows the matching dates in which I applied your formula based on MAX and IF as shown in B4

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Rain gauge

Highest amount of rainfall per day for each month over the years

52.6​

25​

57.2​

37.6​

27.8​

64.6​

12.4​

26.6​

26.2​

24.6​

26.2​

17.6​

=INDEX(Date,MATCH(C16,TBRG,0),0,1)

20/01/2020​

14/02/2020​

5/03/2020​

4/04/2020​

20/05/2020​

9/06/2021​

16/07/2019​

22/08/2020​

26/09/2020​

24/10/2020​

26/09/2020​

22/12/2020​

=MAX(IF(TBRG=C16,Date,0))

20/01/2020​

14/02/2020​

5/03/2020​

4/04/2020​

20/05/2020​

9/06/2021​

16/07/2019​

22/08/2020​

22/11/2020​

24/10/2020​

22/11/2020​

22/12/2020​

Now, my initial problem was that a value for September was shown in the month of November, because The values for September and November are the same, namely 26.2.
When I applied your suggestion then that problem was solved! However, now I got the same problem but in reverse for the month of September (Showing the value for November).
Ahhh, sometimes I hate logic: It's so unforgiving

Any ideas how to make sure that only dates fitting within that month are listed and nothing else?

 

[Saba Sabaratnam]

Hi Luke,

Another IF is needed to filter the data by month.

Please see my worksheet layout below.

Enter the following formula in F6 by pressing CONTROL + SHIFT + ENTER and copy it across.

=MAX(IF($C$2:$C$31=F4,IF($B$2:$B$31=F5,$A$2:$A$31,0),""))

On other note, I am not sure how you are getting your max value for the month.

You could use this array formula to get Max temperature for each month by entering it in F5 and copy it across (CONTROL + SHIFT + ENTER)

=MAX(IF($C$2:$C$31=F4,$B$2:$B$31,0))

Kind regards

saba

 

[LRATOZ]

Hi Luke,

Another IF is needed to filter the data by month.

Please see my worksheet layout below.

Enter the following formula in F6 by pressing CONTROL + SHIFT + ENTER and copy it across.

=MAX(IF($C$2:$C$31=F4,IF($B$2:$B$31=F5,$A$2:$A$31,0),""))

On other note, I am not sure how you are getting your max value for the month.

You could use this array formula to get Max temperature for each month by entering it in F5 and copy it across (CONTROL + SHIFT + ENTER)

=MAX(IF($C$2:$C$31=F4,$B$2:$B$31,0))

View attachment 41408


Kind regards

saba

Thank you Saba!
It works flawlessly. I just customized it to my sheet and it shows the results as expected.
I am also very happy that your solution is based on a nested IF-statement which is universally understood.
Thank you very much for your time and effort. Much appreciated!
I marked your solution as problem solved.
You really made my day. I've been working on this for many days. I am still a novice in Excel but I've got the determination to finish off my project.
Have a nice day!

Luke

 

[Saba Sabaratnam]

I am really glad to help

Probably, you already know that you can extract month from date using the following normal formula.

=MONTH(A2)

Kind regards

Saba

 

[LRATOZ]

I am really glad to help

Probably, you already know that you can extract month from date using the following normal formula.

=MONTH(A2)

Kind regards

Saba

Yes, that's exactly what I've done: I extracted the year and the month in different columns.
Thanks for that tip anyway.
Cheers,

Luke