Wow! This formula is so versatile. Thanks Biff for sharing.

I had so much fun playing with it and came up with the following findings. Please correct me if I am wrong.

Keep SMALL and change "<" to "", I get the kth element in descending order.

Keep "<" and change SMALL to LARGE, I also get the kth element in descending order.

With SMALL, "<" and k = 1, I get the smallest element.

With LARGE, "<" and k = 1, I get the largest element.

With SMALL, "" and k = 1, I also get the largest element.

All this is good only when I have only numbers (right aligned) or only text (left aligned) in the range.

If I have text and numbers in the range, I don't trust any of the above formulae.

e.g. A1:A10 I only have zzz (text) or 999 (number).

I get the same result regardless of whether I use SMALL or LARGE in the formula. I am keeping the "<" in the formula. I don't understand why.

I also don't trust the formula when I have blanks. I only focus on all text or all numbers plus blanks in the range.

e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COU NTIF(rng,"<"&rng),0))

rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination in between. No blanks.

The formula returns aaa as the smallest element. No problem. Now delete aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the blank in A10. ???

I am missing something.

Appreciate input.

Epinn

"T. Valko" wrote in message ...
Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COU NTIF(rng,"<"&rng),0))

Where k = the kth element

Biff

"kjs" wrote in message
...
=Small(A1:A5,2) from
12
15
12
10
17
returns the value 12, but what from
def
ghi
qrs
def
abc
returns "def" as the second lowest, by sort order
or ascii value?
I can get as far as =MIN(CODE(A1:A5)) entered
as an array formula giving 97, the lowest ascii
value of the first letter, but then I come off the rails.
Any ideas?

kjs