Hi!
To find the nth smallest without duplicates:
=INDEX(A10:A20,MATCH(SMALL(B10:B20,X),B10:B20,0))
Replace X with the nth value you're interested in.
OR, use a cell to hold the nth value:
A1 = 2 (or 3, or 5, or 9)
=INDEX(A10:A20,MATCH(SMALL(B10:B20,A1),B10:B20,0))
If there might be duplicates it gets a little more
complicated. The best way to handle this is to "break
ties" by using a rank formula:
In C10 enter this formula and copy down to C20:
=RANK(B10,$B$10:$B$20,2)+COUNTIF($B$10:B10,B10)-1
This will rank the first instance of a duplicate higher
(or lower depending on which REFERENCE argument you use in
the RANK function) than the next instance.
For example:
Tom 100
Sue 100
Tom would get ranked as 1 and Sue would get ranked as 2.
Then you can use the INDEX formula based on the rankings
in column C:
=INDEX(A10:A20,MATCH(SMALL(C10:C20,2),C10:C20,0))
Biff
-----Original Message-----
Hi,
I have a formula....=INDEX(A10:A20,MATCH(MIN
B10:B20),B10:B20,0)) which returns the name in column A
that corresponds to
the minimum number in B10:B20.
I would like a formula to also give me the second least
valued number in
B10:B20. And the third, fourth and fifth.
Also, what will happen if there are two numbers that are
minimum and equal
to each other?
Thank you,
Ken
.
|