If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Find latest date from list and corresponding info
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! |
#3
|
|||
|
|||
Find latest date from list and corresponding info
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! |
Thread Tools | |
Display Modes | |
|
|