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