Returning second smallest unique value

Hello,

I'm creating a spreadsheet and have had difficultly creating a function that returns the second smallest unique value.

For example if my data set is:

2, 2, 3, 3, 4, 5, 6

I'd want the function to return "3". I've tried playing around with the SMALL(array, k) function and trying to use a COUNTIF function for "k" but with no luck.

Any suggestions?

Welcome to the forum.

=SMALL( IF( MATCH(A1:A7, A1:A7, 0) = ROW(A1:A7) - MIN(ROW(A1:A7) ) + 1, A1:A7, ""), 2)

The formula MUST be confirmed with Ctrl+Shift+Enter, not just Enter.

Maybe this array formula:

=MIN(IF($A$1:$G$1 > MIN($A$1:$G$1), $A$1:$G$1, ""))

...confirmed with CTRL-SHIFT-ENTER.

EDIT:
SHG's formula appears to be more robust for getting the third value, then the fourth...if that is a need, too.
If all you ever want is the second smallest, then this one would be enough.

One way...

=MIN(IF($A$1:$A$7>MIN($A$1:$A$7),$A$1:$A$7))

confirmed with cTRL+shift+enter....

If all you ever want is the second smallest, then mine would be simplest.

Yes indeed.

Well, the question remains, why use VBA for something accomplished simply with a formula?