=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