look up maximum date in a range

[Hank]

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

 

[Stefi]

=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:

 

[T. Valko]

=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))

 

[Hank]

Thanks Stefi, I tried and it returned the result exactly same value as End
date, it actually didn't perform comparison.


Lease # Start date End date RESULT
(=MAX(--(A2:A5="001")*C2:C5))
001 2/1/2002 1/31/2005 1/31/2005
001 2/1/2005 1/31/2008 1/31/2008
002 1/1/2006 4/30/2009 4/30/2009
002 5/1/2009 10/31/2012 10/31/2012

it is not what I want to come up... ?
Hank

 

[Hank]

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]

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.

 

[Hank]

Thanks Biff, I never used array formula before, it certainly work by hit crtl
shift enter. Thanks for your help... Also I come up another solution by use
lookup and max together. =MAX(LOOKUP(A4,A:A,C:C)) A4 is lease #, AA is lease
# column and CC is end date column, it comes the same result.

Hank

 

[T. Valko]

=MAX(LOOKUP(A4,A:A,C:C))

You don't need the MAX function:

=LOOKUP(A4,A:A,C:C)

Just be careful with that. It will do what you want as long as the data in
column A is sorted in ascending order *and* there is always an exact match
of the lookup value.

 

[Stefi]

Thanks for your advices!
Stefi


„T. Valko†ezt írta: