Find nth lowest date in range

D

DutchKevin

Board Regular
Joined
Apr 13, 2011
Messages
133
Office Version
  1. 365
Platform
  1. Windows
Hi,
Somehow the standard formula =SMALL($A$1:$A$28;B1) does not give me the 3rd smallest date.

In the range I would have a set of dates.
In B1, and down, a running number from 1 to e.g. 10.
There are 4 unique dates in my range, so i would expect 4 results, no more.
But somehow the result acts more like a sorting. Showing the 5 "smallest equal dates" first, then it lists the three "2nd smallest" dates etc etc.
What am i missing here?
There are no errors in the range, no blanks, no Null.....

thanks
Kevin
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Should be able to accomplish with an array function.

Excel 2010
ABC
112/4/2016112/1/2016
212/5/2016212/2/2016
312/4/2016312/3/2016
412/1/2016412/4/2016
512/4/2016512/6/2016
612/3/20166
712/2/20167
812/6/20168
912/3/20169
1012/5/201610
1112/2/2016
1212/5/2016
1312/6/2016
1412/5/2016
1512/6/2016
1612/1/2016
1712/1/2016
1812/2/2016
1912/6/2016
2012/1/2016
2112/7/2016
2212/4/2016
2312/1/2016
2412/6/2016
2512/7/2016
2612/3/2016
2712/3/2016
2812/3/2016
Sheet1


Your formula works for C1, but you'll need an array formula for C2:C10:


Excel 2010
BC
2212/2/2016
3312/3/2016
4412/4/2016
5512/6/2016
66
77
88
99
1010
Sheet1
Cell Formulas
RangeFormula
C2{=IFERROR(SMALL(IF($A$1:$A$28>C1,$A$1:$A$28),B2),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Kevin, as you've found the SMALL function works as a sort, where if duplicate values are found, they are all listed in order. You can determine which index to request by counting the number of values equal to or greater than the value in the row above, like this:

ABCDE

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/4/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/1/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/1/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/1/2016[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/5/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/2/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/2/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/2/2016[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/4/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/3/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/3/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/3/2016[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/1/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/4/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/4/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/4/2016[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/4/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/5/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/6/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/5/2016[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/3/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/6/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/6/2016[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/2/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/7/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/7/2016[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/6/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/3/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/5/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/2/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/5/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/6/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/5/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/6/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/1/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/1/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/2/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/6/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/1/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/7/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/4/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/1/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/6/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/7/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/3/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/3/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12/3/2016[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=MIN(A1:A28)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(C1="","",IFERROR(SMALL($A$1:$A$28,COUNTIF($A$1:$A$28,"<="&C1)+1),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$28>D1,$A$1:$A$28),B2),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$28>E1,$A$1:$A$28),1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the MIN function in C1, then the C2 function in, then drag down.

CalcSux, your array formula is a viable option, but you need to replace the B2 in your formula with 1. See the difference in columns D and E.

Hope this helps!


Edit:

If you actually do want to use an index to find the nth lowest unique value, without having to use the value in the row above, you can use this array formula:

=IFERROR(SMALL(IF(MATCH($A$1:$A$28,$A$1:$A$28,0)=ROW($A$1:$A$28)-ROW($A$1)+1,$A$1:$A$28),B1),"")
confirmed with Control+Shift+Enter.
 
Last edited:
Upvote 0
Thanks Eric and CalcSux for helping out. Both your replies has helped me in getting the required output.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Find the Nth Largest with Multiple Criteria
Replies
7
Views
372
nemmi69
nemmi69
JeffGrant
  • Solved
Finding the nth Occurance of an associate value.
Replies
5
Views
423
Peter_SSs
Peter_SSs
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
Dave_O
  • Solved
Find the nth occurrence of a word, to find the column_num, to find the student name.
Replies
3
Views
521
Marcelo Branco
M

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top