Thread: Avoiding #NUM!
View Single Post
  #10   Report Post  
Domenic
 
Posts: n/a
Default

Beautiful...just the type of thing I was looking for! Thanks Harlan!
Much appreciated!

In article .com,
"Harlan Grove" wrote:

But it'd be more efficient still not to use SMALL or the volatile
OFFSET at all. It requires using different formulas for the top most
result and the subsequent results.

C1 [array formula - topmost cell]:
=INDEX($A$1:$A$8,MATCH(0,COUNTIF($A$1:$A$8,"<"&$A$ 1:$A$8),0))

C2 [array formula - subsequent, fill down as needed]:
=IF(COUNTIF($A$1:$A$8,""&C1),INDEX($A$1:$A$8,MATC H(COUNTIF($A$1:$A$8,
"<="&C1),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)), "")