second minimum value

[Ken]

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

 

[Biff]

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

 

[Ken]

Thank you VERY much. It's exactly what I needed.
Enjoy your day.
Ken

 

[Biff]

You're welcome! Thanks for the feedback.

Biff

-----Original Message-----
Thank you VERY much. It's exactly what I needed.
Enjoy your day.
Ken




.