rank large value by different range and without duplicate

Hi Kar2rost and welcome to the Forum.

This answer ahs been revised now that you have attached a file (as an Answer- it should be part of your question really). Usrs should note that you use right to left columns.

Your question is actually about using a complex formula to return the highest ranked number (without duplicates) but for multiple ranges. In J33, your formula for a single range is:

=IFERROR(AGGREGATE(14,6,INDIRECT($J$29)/(MATCH(INDIRECT($J$29),INDIRECT($J$29),)=ROW($A$1:INDEX($A:$A,COUNT(INDIRECT($J$29))))),""&E33),"")

I suggest you use LARGE anstead, combined with COUNTIF to eliminate duplicates.

In the attached revised file, cell N38 (green)  has the range containing all values (and headings- which LARGE will ignore):

'2403!m38:m214

In cell K3, we get the highest value (ranked 1st) from that range using:

=LARGE(INDIRECT($N$38),1)

If we changed the second argument (k, the rank position) from 1 to a 2, we'd get the same again (since that value is duplicated). Instead, we count how many duplicates of K33 are in the range then add 1. The second argument becomes (in bold):

=LARGE(INDIRECT($N$38),COUNTIF(INDIRECT($N$38),">="&K33) + 1)

then copy that down and all duplicates will be ignored.You can either remove them from your range (e.g put them in anopther column) or adjust the formula in K33 to ignore them i.e.

=LARGE(INDIRECT($N$38),7)

Hope this works for you (please remember to mark this answer as Selected if so)