Lookup the most recent date from a column....involves 3 columns

[Steve]

Can anyone help me with the problem below? I need a formula that will
give me the quantity in column B that corresponds to the latest date
in column C for a given fruit in column A…..

The results need to show up in a forth column as I've entered them
manually below. My spreadsheet is much more involved than my example
(700+ rows), but a formula that will do what I'm asking for below will
do wonders..

Fruit Qty Date Results

Apples 12 8/1/2004
Apples 4 7/22/2004
Apples 6 8/3/2004 6
Apples 3 6/5/2004
Apples 5 7/2/2004
Apples 4 7/12/2004
Apples 2 8/2/2004
Oranges 7 3/2/2004
Oranges 4 7/15/2004 4
Oranges 10 7/13/2004
Oranges 2 7/1/2004
Pears 1 2/1/2004
Pears 6 7/28/2004 6
Pears 8 6/30/2004
Pears 3 6/12/2004
Pears 6 5/3/2004
Pears 5 7/20/2004
Pears 11 4/3/2004
Pears 15 6/3/2004
Pears 3 7/22/2004

 

[Vasant Nanavati]

=IF(C1=MAX((A1=A$1:A$20)*(C$1:C$20)),B1,"")

entered as an array formula with <Ctrl> <Shift> <Enter> and copied down.
Adjust the row references to suit.

 

[Steve]

Vasant,

Your response worked perfectly... Since I'm a bit of a novice at this
stuff, if you have the time, could you do me a favor and briefly
explain exaclty how the formula you wrote does what it does?


Thanks again,

Steve

 

[Vasant Nanavati]

It's not easy to explain (at least for me), but let's start on the inside
and work outwards:

A1=A$1:A$20

This yields an array of TRUEs and FALSEs (which are equivalent to 1s and 0s
in multiplication). The TRUEs will correspond to all the items in column A
that are the same as A1.

(A1=A$1:A$20)*(C$1:C$20)

This array is then multiplied by the corresponding dates. Now you have an
array of 0s and dates. The zeros correspond to the items which don't match
A1; the dates correspond to the items that do. So the array consists of
zeros, and dates for the items matching the original item (e.g., "apples").

MAX((A1=A$1:A$20)*(C$1:C$20))

This picks the largest item in the array, which would be the latest date for
"apples".

=IF(C1=MAX((A1=A$1:A$20)*(C$1:C$20)),B1,"")

If the date in this row corresponds to the calculated maximum date for the
item in the row, show the quantity in the row; otherwise show nothing.

I hope this wasn't too confusing!