Find latest date from list and corresponding info

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!
 
T

T. Valko

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.
 
T

Teethless mama

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top