Find latest date from list and corresponding info

Hi,

I have a list like the below and am trying to:

A) Find most recent date of each color group (ColC)
B) Then, from that date, find corresponding number code (ColA)

ColA ColB ColC
1001 9/1/10 Red
1002 4/1/05 Red
1003 8/1/09 Blue
1004 9/1/08 Blue
1005 1/1/10 Blue

Thanks for your help!

Try these...

Data in the range A2:C6

E2 = Red

Formula in F2 array entered** for the max date:

=MAX(IF(C2:C6=E2,B2:B6))

Formula in G2 array entered** for the code:

=INDEX(A2:A6,MATCH(1,IF(C2:C6=E2,IF(B2:B6=F2,1)),0 ))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"MCRH" wrote in message
...
Hi,

I have a list like the below and am trying to:

A) Find most recent date of each color group (ColC)
B) Then, from that date, find corresponding number code (ColA)

ColA ColB ColC
1001 9/1/10 Red
1002 4/1/05 Red
1003 8/1/09 Blue
1004 9/1/08 Blue
1005 1/1/10 Blue

Thanks for your help!

Assuming your unit color in D2 and down

In E2:
=INDEX($A$2:$A$6,MATCH(1,($B$2:$B$6=MAX(($C$2:$C$6 =D2)*$B$2:$B$6))*($C$2:$C$6=D2),))

ctrl+shift+enter, not just enter
copy down


"MCRH" wrote:

Hi,

I have a list like the below and am trying to:

A) Find most recent date of each color group (ColC)
B) Then, from that date, find corresponding number code (ColA)

ColA ColB ColC
1001 9/1/10 Red
1002 4/1/05 Red
1003 8/1/09 Blue
1004 9/1/08 Blue
1005 1/1/10 Blue

Thanks for your help!