All of the formulas suggested are array formulas.

Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Hank" wrote in message
...
Hi Biff

I tried your formula as well =MAX(IF(A2:A5000="001",C2:C5000))
it comes up the maximum of column of "end date" which is 10/31/2012,
regardless lease #

It is not what I want to get... can u please help me more?
Thanks
Hank




"T. Valko" wrote:

=MAX(--(A2:A5="001")*C2:C5)

Since you're multiplying the boolean result (A2:A5="001") by the date (a
number) C2:C5 you don't need the double unary "--".

=MAX((A2:A5="001")*C2:C5)

On large ranges it's slightly more efficient to use the IF version:

=MAX(IF(A2:A5000="001",C2:C5000))

--
Biff
Microsoft Excel MVP


"Stefi" wrote in message
...
=MAX(--(A2:A5="001")*C2:C5)
It's an array formula, confirm it with Ctrl+Shift+Enter!

Adjust ranges and Lease # constant as required!

Regards,
Stefi

"Hank" ezt írta:

Hi there,

I have a few lease contracts, they have different lease periods, some
of
them has different renewal terms. I would like to look up the latest
date
in
a lease, such as

Lease # Start date End date
001 2/1/2002 1/31/2005
001 2/1/2005 1/31/2008
002 1/1/2006 4/30/2009
002 5/1/2009 10/31/2012

I need a formula to come up lease 001's end date is 1/31/2008 (that's
compare two end date in lease 001 in maximum end date between
1/31/2005
and
1/31/2008)
002's end date is 10/31/2012

Thanks alot and have a great weekend

Hank