Finding the nth value if another column meets a criteria

[crowfan65]

okay here is a sample of the data

1 50
2 1
3 52
4 22
5 3
6 9
7 55
8 52
12 24
13 268
1 19
2 42
3 0
4 44
5 43
6 7
7 33
8 9
9 16
10 5
12 24
13 242
1 111
2 221
3 100
4 9
12 19
13 460
1 0
2 0
3 11
4 8
5 22
6 14
7 0
8 5
9 1
10 3
11 0
12 8
13 72


I know if I use dmax for only where first column equals 13 I get 460 but how do I get the second highest value for only those rows that have 13 in the first column (expect the answer to be 268). Then I want to do the same for 3rd, 4th highest etc.
I know large does it for one column and not only when the first column matches a designated criteria.

Help would be appreciated and apologies if this has been covered before but I couldn't find any earlier post.

Cheers crowfan65

[DonkeyOte]

You can use LARGE in an array such that:

Please Login or Register  to view this content.

When the array in E1 is set the above will appear encased within { }

[crowfan65]

You can use LARGE in an array such that:

Please Login or Register  to view this content.

When the array in E1 is set the above will appear encased within { }

I actually tried something like that but now I realise my mistake, it doesn't like a reference being complete columns (eg. A:A=D1,B:B), you have to have numbers in it.

Thanks very much

[DonkeyOte]

pre 2007 you can't use entire column references with array formulae... nor should you want to -- with array's you really should keep range size to a minimum - the larger the range the greater the adverse impact on performance.