Index and Match, Don't Want Duplicates

The easiest way to achieve this is to use a helper table, this can be hidden, for example with a grouping button.

In AN2

Formula:

Please Login or Register  to view this content.

To check the duplicates for 21/7 (yesterday) temporarily change this to
=MATCH(TODAY()-1,B2:AF2,0) or just 21

See this workbook for the remaining formulae.

Hi Marcol,
Thanks so much, it works but I would like to tweak it when two or more temperatures are identical, sometimes it puts the matching year in ascending order, i would like it to be in decending order, like this:
now:
84.0 2007
84.0 2010
would like it to have the year in decending order like this:

84.0 2010
84.0 2007
Thanks.

Okay we just reverse the order of the COUNTIF() counter
In AD3 old formula

Formula:

Please Login or Register  to view this content.

New formula

Formula:

Please Login or Register  to view this content.

Two points to note.
1/. In the starting formula AN3:AN$15 covers your table depth.
If you have more rows, say going back another 5 years, then the starting range will be AN3:AN$20
Note that the last cell is now your anchor.

2/.Your data has an indeterminate number of decimal points, Excel will only display up to 16 digits including any decimal point, formatting for any more will only display additional zeros.

It might be possible that by adding the tie-breaker,some numbers could be wrongly promoted.
To minimize this possibility I've decreased the added counter from n/100 to n/10^9

Better that you decide on how many significant figures you need and round either the source data or the helper (column AN) accordingly.

Oops, missed that!

Okay, the easiest way to handle that, I hope, is to use seperate tie-breaker columns for Large and Small
In AO3 (Latge)

Formula:

Please Login or Register  to view this content.

In AP3 (Small)

Formula:

Please Login or Register  to view this content.

You can now disregard point 1 in post #4

See this workbook for the range reference changes in the Results Table.