Find the latest duplicated value based on year.

Hi All,

If I have 2 columns, one containing the year and the second one containing duplicate values, how can I find only the last instance of the duplicated value based on the latest year?
Spreadsheet attached.


Many thanks in advance,
Paul

Try:

=IF(COUNTIFS($A$2:$A$25,">"&A2,$B$2:$B$25,B2),"",
IF(COUNTIFS($A$2:$A$25,A2,$B$2:$B$25,B2)=COUNTIFS($A$2:A2,A2,$B$2:B2,B2),"Last instance",""))

Hi!

Another option could be:
[C2] : =IF(ROW(B2)=LOOKUP(2,1/(B$2:B$25=B2)/(A$2:A$25=MAX(A$2:A$25*(B$2:B$25=B2))),ROW(B$2:B$25)),"Last Instance","")

Blessings!

Hi,

Both formulas work. Thanks a lot.