Excel nth smallest DISTINCT value

Question

I have a set of values, the Small function gives me the ability to get the top 5 smallest values. but it doesnt take into account duplicates. I want to only see each value once. for example:

1 2 2 3 4 5

i want to output 1,2,3,4,5 not 1,2,2,3,4

I am putting the output into 5 different columns with the formula Small(A1:A20,[1-5]) but im not sure how to tell it to only look at each distinct value in the range

Answer 1

Here is another option:

Formula in C1:

=SMALL($A:$A,COUNTIF($A:$A,"<="&B1)+1)

Drag right...

Answer 2

Here is another option and in single formula

Assume your data 1 2 2 3 4 5 put in A1:A6

B1, left blank

In C1, formula copied cross right until blank

=IFERROR(1/(1/AGGREGATE(15,6,$A$1:$A$20/($A$1:$A$20>B1),1)),"")

Edit : AGGREGATE is a function for Excel 2010 or above

Answer 3

If one has access to the dynamic array formulas (currently only available to office 365 insiders) one can just put this in the first cell and the results will spill across:

=SMALL(UNIQUE(A:A),SEQUENCE(,5))

---

Other wise we need to use some array formula in a specific manner.

We must have something besides a number in the cell directly preceding where we put the formula in the first cell. So if I am putting the formula in C1, B1 must not contain one of the numbers as we need to refer to it.

Put this in C1:

=SMALL(IF(COUNTIF($B$1:B$1,$A$1:$A$20)=0,$A$1:$A$20),1)

Being an array formula it must be confirmed with Ctrl-Shift-enter instead of Enter when exiting edit mode. Then copy over 5 columns.

---

If one cannot leave the cell B1 without a number then we must get the array another way:

Put this array formula in the first cell:

=SMALL(INDEX($A:$A,N(IF({1},MODE.MULT(IF(MATCH($A$1:$A$20,$A:$A,0)=ROW($A$1:$A$20),ROW($A$1:$A$20)*{1,1}))))),COLUMN(A:A))

Being an array formula it must be confirmed with Ctrl-Shift-enter instead of Enter when exiting edit mode. Then copy over 5 columns.