[Marcel]

Is there a function to get in a cell the maximum text "value" of a list
(e.g. T1, T2a, T2b).
Like MAX for numbers, part of this functionality is embedded in the
sort/filter commando's.

[Ron Coderre]

Depending on the list structu

With
A1:A30 containing text values (no duplicates, some blanks are OK)

This formula returns the "max" text value:
B1:
=INDEX(A1:A30,MATCH(COUNTA(A1:A30)-1,INDEX(COUNTIF(A1:A30,"<"&A1:A30),0),0))

Eample:
A1: Bravo
A2: (blank)
A3: Zulu
A4: Charlie

The formula returns: Zulu

BUT...if there may be duplicates

Either this ARRAY FORMULA
(committed with Ctrl+Shift+Enter, instead of Enter)
B1: =INDEX(A1:A30,MATCH(MAX(COUNTIF(A1:A30,"<"&A1:A30) ),
COUNTIF(A1:A30,"<"&A1:A30),0))

or...this longer...but, non-array formula:
B1:
=INDEX(A1:A30,MATCH(MAX(INDEX(COUNTIF(A1:A30,"<"&A 1:A30),0)),
INDEX(COUNTIF(A1:A30,"<"&A1:A30),0),0))

OR...if there may be ALL blanks:
Wrap any of those formulas within this structu
B1: =IF(COUNTA(A1:A30),the_formula,"No items listed")
....Committing with Enter or Ctrl+Shift+Enter, depending on the formula you
use.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Marcel" wrote in message
...
Is there a function to get in a cell the maximum text "value" of a list
(e.g. T1, T2a, T2b).
Like MAX for numbers, part of this functionality is embedded in the
sort/filter commando's.