Help! How do I return the latest of a series of dates using Vlooku

[Guest]

With nearly 3000 entries, and growing, could someone help me to make this
work more easily? Sorry, what I meant to say was, 'could someone help me to
make this work'!
I have a list of client references in one column of a s'sheet showing when
clients invested. In another column, I have the dates when they invested;
often there will be several dates over the last 5 years for each client.
In the last column, and for each row of a client reference, I need to return
the latest of the dates that relate to each client reference and this client
list will continue to grow as additional investments are made, so the latest
date will need to change to reflect the latest, last one added.

As an example of what I think I'm looking to achieve...
A B C
Client Date Latest
Ref Invested date
X01 1/1/2006 1/1/2006
X09 3/5/2006 28/7/2007
X23 26/1/2007 26/1/2007
X78 19/3/2007 19/3/2007
X09 28/7/2007 28/7/2007

Is there a simple formula to use? I'm presuming that Vlookup is approapriate.
Or am I expecting too much of Excel?
I hope there's a super guru out there!
Thanks
Geoff.

 

[Ron Rosenfeld]

With nearly 3000 entries, and growing, could someone help me to make this
work more easily? Sorry, what I meant to say was, 'could someone help me to
make this work'!
I have a list of client references in one column of a s'sheet showing when
clients invested. In another column, I have the dates when they invested;
often there will be several dates over the last 5 years for each client.
In the last column, and for each row of a client reference, I need to return
the latest of the dates that relate to each client reference and this client
list will continue to grow as additional investments are made, so the latest
date will need to change to reflect the latest, last one added.

As an example of what I think I'm looking to achieve...
A B C
Client Date Latest
Ref Invested date
X01 1/1/2006 1/1/2006
X09 3/5/2006 28/7/2007
X23 26/1/2007 26/1/2007
X78 19/3/2007 19/3/2007
X09 28/7/2007 28/7/2007

Is there a simple formula to use? I'm presuming that Vlookup is approapriate.
Or am I expecting too much of Excel?
I hope there's a super guru out there!
Thanks
Geoff.

You can use the **array** formula in the form of:

=MAX((A3=$A$3:$A$1000)*$B$3:$B$1000)

To enter an **array* formula; after entering the formula into the cell or
formula bar, confirm by holding down <ctrl><shift> while you hit <enter>. XL
will place braces {...} around the formula.

You can also use the NON-array formula:

=SUMPRODUCT(MAX((A3=$A$3:$A$10)*$B$3:$B$10))

entering it normally.

Note that you cannot use a reference to an entire column in these kinds of
formulas. (e.g. you cannot use A:A)
--ron

 

[Pete_UK]

Hi Geoff,

in the absence of a super guru, I can offer you this array* formula in
C2:

=MAX(IF((A$2:A$3000=A2),B$2:B$3000))

*As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit
it, rather than the usual ENTER. If you do this correctly, then Excel
will wrap curly braces { } around the formula when viewed in the
formula bar - you must not type these yourself.

Copy the formula down into C3:C3000 (or however many entries you
have).

Hope this helps.

Pete

 

[Guest]

NON array formula

=MAX(INDEX((A2:A100="a")*B2:B100,0))

Just hit Enter

 

[Guest]

Thanks, I'll give it a try later. Had some other responses, too, so I'll see
which I prefer.
Thanks again

Geoff.