Using Small
 

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?

Using Small
 

=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" wrote in message
...
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?

Using Small
 

Cheers Bob. Very good
"Bob Phillips" wrote in message
...
=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" wrote in message
...
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?