On Tue, 9 Oct 2007 03:36:00 -0700, Geoff Newham
wrote:

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