Don't get me wrong. You gave a perfect solution for the OP's request.

Yes, I got carried away. I was "greedy" and I thought I could use that formula on numbers as well. It did seem to work when I had all numbers and no blanks.

Okay, I won't drive myself crazy with Large/Small, "<" and "" etc.

Forgive me and I shall give everyone and myself a break hopefully soon.

Thanks for the wonderful gifts. If I understand correctly, both formulae return the smallest item. I won't worry about the nth element in a range of text and numbers.

Epinn

"T. Valko" wrote in message ...
Don't get carried away with </ Large/Small! It can be confusing! <bg

That particular formula only works with TEXT and I assumed the rng only
contained TEXT. I didn't see any numbers and/or blank/empty cells
represented in the sample.

This formula works for TEXT only and accounts for blank/empty cells (no
error trap) (array entered):

=INDEX(rng,MATCH(SMALL(IF(rng<"",COUNTIF(rng,"<"& rng)),ROWS($1:1)),IF(rng<"",COUNTIF(rng,"<"&rng)) ,0))

This formula works for both TEXT and NUMBERS and accounts for blank/empty
cells (array entered). Note: without the error trap any blank/empty cells
will be included as 0's at the end of the list:

=IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,MATCH(SMALL( COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100 000*ISBLANK(rng),ROWS($1:1)),COUNTIF(rng,"<"&rng&" ")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),0)), "")

It will sort numbers first, then the text.

Biff

"Epinn" wrote in message
...
One more thing I just discovered.

LARGE and "" is exactly the same as SMALL and "<" i.e. ascending order.

Epinn

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