Using Small

[PH NEWS]

Hi all

How would I add the SMALL function to the formula below to ensure my results
were sorted?

=INDEX($A$3:$A$11,MATCH(0,COUNTIF($B$2:B2,$A$3:$A$11),0))

Further more, would anyone be so kind as to explain how the SMALL function
works in a formula like this or point me in the right direction of an
explanation?

 

[Bob Phillips]

=INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<"&
$A$2:$A$11),ROW(1:1)),COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),0))

It works by counting how many items are smaller than each data element, and
then uses SMALL with the row number to extract from the list that count,
which it matches back against the same count list to get the index, which it
passes to INDEX to get the actual element.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[PH NEWS]

Cheers Bob. Very good

=INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<"&
$A$2:$A$11),ROW(1:1)),COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),0))

It works by counting how many items are smaller than each data element, and
then uses SMALL with the row number to extract from the list that count,
which it matches back against the same count list to get the index, which it
passes to INDEX to get the actual element.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)