I can't think of an example why one would need to retrieve the Nth element.

http://tinyurl.com/2cvlpd

Biff

"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.