Return top 10 dates

this would be one way...


EDIT: BTW, your results only match mine if you reverse the order in the rank so instead of this =RANK(C3,$C$3:$C$68,0)+COUNTIF($C$3:C3,C3)-1
make it this... =RANK(C3,$C$3:$C$68,1)+COUNTIF($C$3:C3,C3)-1

hi Jonathan. it looks like the smallest value comes up first? 29 Nov 13 had 1 in column C. so if that's what you want & the values don't repeat, try:
=INDEX($B$3:$B$68,MATCH(SMALL($C$3:$C$68,ROWS(D$3:D3)),$C$3:$C$68,0))

if the values do repeat, then try this array formula:
=INDEX($B$3:$B$68,MATCH(SMALL($C$3:$C$68+ROW($C$3:$C$68)/1000,ROWS(D$3:D3)),$C$3:$C$68+ROW($C$3:$C$68)/1000,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

if you want the biggest number, then change SMALL to LARGE

=INDEX($B$3:$B$68,MATCH(SMALL($C$3:$C$68,ROW()-2),$C$3:$C$68,0))

Or try this formula in cell D3

benishiryo was a little faster

You may also try this array formula ( confirm with Ctrl+Shift+Enter )

Please Login or Register  to view this content.