Max Lookup formula
 

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

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

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

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" wrote in message
...
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

Excellent. Thank you Dave.

Dave Peterson wrote:
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 wrote:

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

Thank you. Gotto love that SUMPRODUCT.

RagDyer wrote:
Try this in H1:

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

And copy down.

Unnecessary, probably inefficient too.

sam wrote:
Thank you. Gotto love that SUMPRODUCT.

RagDyer wrote:

Try this in H1:

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

And copy down.