Find latest date from list and corresponding info

  • Thread starter Thread starter MCRH
  • Start date Start date
M

MCRH

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.
 
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
 
Back
Top