### Tags: a10a20, b10b20, column, corresponds, excel, formula, index, match, microsoft, min, minimum, msdn, number, returns, second, software, value

# second minimum value

On Microsoft » Microsoft Excel

5,804 words with 3 Comments; publish: Thu, 22 May 2008 21:51:00 GMT; (30678.13, « »)

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

*http://excel.questionfor.info/q_microsoft-excel_401992.html*

All Comments

Leave a comment...

- 3 Comments
- 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

>

>.

>

#1; Thu, 22 May 2008 21:52:00 GMT

- Hi!
- Thank you VERY much. It's exactly what I needed.
Enjoy your day.

Ken

"Biff" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

news:0a7701c529eb$637eb9b0$a601280a.excel.questionfor.info.phx.gbl...

> 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

>>

>>.

#2; Thu, 22 May 2008 21:53:00 GMT

- Thank you VERY much. It's exactly what I needed.
- You're welcome! Thanks for the feedback.
Biff

>--Original Message--

>Thank you VERY much. It's exactly what I needed.

>Enjoy your day.

>Ken

>"Biff" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

>news:0a7701c529eb$637eb9b0$a601280a.excel.questionfor.info.phx.gbl...

>> 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

>>

>>.

>

>.

>

#3; Thu, 22 May 2008 21:54:00 GMT

- You're welcome! Thanks for the feedback.