Home 
Search 
Today's Posts 
#1




second minimum value
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




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




Thank you VERY much. It's exactly what I needed.
Enjoy your day. Ken "Biff" wrote in message ... 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 . 
#4




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" wrote in message ... 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 . . 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Finding minimum value across selected rows of an array  Excel Worksheet Functions  
Hiding Values Below Z Axis Minimum Value on Axis Charts  Charts and Charting in Excel  
How do I get "minimum value" in a range to NOT return zero?  Excel Worksheet Functions  
lookup  return minimum value  Excel Worksheet Functions  
Sum minimum values in a pivot table  Excel Worksheet Functions 