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!