I'll explain the formula I suggested.

Let's use this smaller dataset:

...........A..........B..........C
1........X.........20..........2
2........X.........15..........3
3........X.........29..........1
4........Z.........50..........1
5........Z.........14..........2

The formula is counting how many entries meet the criteria and the result is
essentially a "rank".

=SUMPRODUCT(--(A$1:A$5=A1),--(B1<B$1:B$5))+1

Each of these expressions will return an array of either TRUE or FALSE:

(A$1:A$5=A1)
(B1<B$1:B$5)

A$1=A1 = T
A$2=A1 = T
A$3=A1 = T
A$4=A1 = F
A$5=A1 = F

B1<B$1 = F
B1<B$2 = F
B1<B$3 = T
B1<B$4 = T
B1<B$5 = F

The "--" coerces the TRUE or FALSE to 1 (TRUE) or 0 (FALSE)

--(A$1:A$5=A1)
--(B1<B$1:B$5)

A$1=A1 = T = 1
A$2=A1 = T = 1
A$3=A1 = T = 1
A$4=A1 = F = 0
A$5=A1 = F = 0

B1<B$1 = F = 0
B1<B$2 = F = 0
B1<B$3 = T = 1
B1<B$4 = T = 1
B1<B$5 = F = 0

The 2 arrays of 1's and 0's are then multipled together:

1 * 0 = 0
1 * 0 = 0
1 * 1 = 1
0 * 1 = 0
0 * 0 = 0

Then SUMPRODUCT adds up the results of that multiplication:

=SUMPRODUCT({0,0,1,0,0}) = 1

And the final step is to add 1:

=SUMPRODUCT(1) +1 = 2

So, B1 (20) is the 2nd largest value corresponding to "X".

You can do a reverse "rank" (lowest ranked higher) by simply changing the
"<" to "" :

=SUMPRODUCT(--(A$1:A$5=A1),--(B1B$1:B$5))+1

Biff

wrote in message
oups.com...
On Jun 1, 7:27 pm, Bernd wrote:
Or

=SUMPRODUCT(--(A$2:A$11=A2),--(C2<C$2:C$11))+SUMPRODUCT(--(A
$2:A2=A2),--(C2=C$2:C2))

if you need different ranks for identical values (first appearing get
higher ranks).

Regards,
Bernd

Ok both these are awesome. Would really appreciate it if you could
explain how they are working as it looks like very powerful
functionality.
Cheers
Matt