However, both formulae give #NUM! error if N is larger
than the number of unique values.

That's true. It could be trapped but you'd have to define what to trap. If N
uniques what result would you want?

It'll also error if there are no numbers in the range.

Biff

"Epinn" wrote in message
...
Interesting timing. As usual, I learned something from you.


=SMALL(IF(ISNUMBER(rng),IF(MATCH(rng,rng,0)=ROW(rn g)-MIN(ROW(rng))+1,rng)),N)

I don't really need "" in the formula. FALSE is fine.

Checking for ISNUMBER is good as it takes care of blanks.

However, both formulae give #NUM! error if N is larger than the number of
unique values. I won't worry about it though.

Your example makes sense. I was thinking of integers and I scratched my
head.

I won't worry about Nth unique element in a data set of *text* only.

Epinn

"T. Valko" wrote in message
...
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.