This is fun. The following also works (and doesn't need to be array
entered):

=LOOKUP(2,1/((COUNTIF($A$1:$A$20,""&$A$1:$A$20)=0)*($A$1:$A$2 0<"")),$A$1:$A$20)

Richard


T. Valko wrote:
Yeah, that'll work. And to account for possible empty cells and/or formula
blanks:

=INDEX(A1:A20,MATCH(0,IF(A1:A20<"",COUNTIF(A1:A20 ,""&A1:A20)),0))

Biff

"RichardSchollar" wrote in message
ups.com...
Or maybe:

=INDEX($A$1:$A$20,MATCH(0,COUNTIF($A$1:$A$20,""&$ A$1:$A$20),0))

Again array entered (ie CSE).

Richard





T. Valko wrote:
Try this:

Array entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

If there will *NEVER* be any empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(COUNTIF(A$1:A$20,"<"&A $1:A$20),1),COUNTIF(A$1:A$20,"<"&A$1:A$20),0))

If there may be empty cells within the range:

=INDEX(A$1:A$20,MATCH(LARGE(IF(A$1:A$20<"",COUNTI F(A$1:A$20,"<"&A$1:A$20)),1),IF(A$1:A$20<"",COUNT IF(A$1:A$20,"<"&A$1:A$20)),0))

Biff

"S" wrote in message
...
Hi,

I wonder if anyone can help me. I've tried to use the max function but
it
only seems to work with numbers.

Is it even possible to do what I want to do. I have a list of names and
I
want to find Zachary for example as its the last name alphabetically in
an
unsorted list of names.

I need to do this in a formula versus sorting.

Any help greatly appreciated.

Thanks

S