Max Lookup formula

[sam]

Hi All

In column A I have an ID number which can repeat a number of times. In
column B I have Dates eg:

A B
xyz 02/05/2005
xyz 20/05/2005
xyz 25/05/2005
abc 03/05/2005
abc 05/05/2005
abc 01/05/2005

The data is not sorted by date.

In another range, say column G, I have a unique list of IDs eg:

G
xyz
abc

I would like to enter a formula in column H to give me the maximum date
for each ID so my result would be

G H
xyz 25/05/2005
abc 05/05/2005

Many thanks
Sam

 

[Dave Peterson]

One way:

=MAX(IF(A1:A100=G1,B1:B100))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Extend the range, but don't use the whole column.

And format the cell with the formula as a date.

 

[RagDyer]

Try this in H1:

=SUMPRODUCT(MAX(($A$1:$A$10=G1)*($B$1:$B$10)))

And copy down.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[sam]

Excellent. Thank you Dave.

One way:

=MAX(IF(A1:A100=G1,B1:B100))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Extend the range, but don't use the whole column.

And format the cell with the formula as a date.

 

[sam]

Thank you. Gotto love that SUMPRODUCT.

 

[Aladin Akyurek]

Unnecessary, probably inefficient too.

Thank you. Gotto love that SUMPRODUCT.