I use them to retrieve the Nth element in a data set of numbers or text.

Why do things the hard way?

For numbers I can just use

=SMALL(rng,N)

This is good if there are no duplicates.

Epinn

"Epinn" wrote in message ...
Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula à ascending order



=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula à descending order



I use them to retrieve the Nth element in a data set of numbers or text.



They work fine if there are no duplicates.



Tonight I have discovered a formula created by David Hager which takes care of duplicates. But it is only good for numbers.



=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- ascending order



=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- descending order



Don't know if I have missed anything. Biff, I will put them away in my stash although I can't think of an example why one would need to retrieve the Nth element.



Comments welcome from all.