Lookup Two Columns based on Highest Date

[Papi]

I have a spreadsheet that has an upper section (rows 1-20) and a lower section (rows 21 - to last row). A macro is used to enter new commentary in the lower section at row 21 and enters the date using =Now() in Cell A21 (replaced then with Copy, Paste Special, Value) and then one of six “two digit” codes into cell B21 i.e. AB, KM etc. and a description is manually entered into cell C21 (there are other columns but they do not apply). This pushes all previous rows downward each time.

The bottom section from row 21 down has the following columns that I need to extract information from

Column A Date
Column B Code
Column C Description

Sample data

Row 21 7/27/2011 3:30:16 PM GH Call client
Row 22 7/27/2011 3:18:24 PM AB Setup booking
Row 23 7/26/2011 5:45:29 PM GH Phone for appointment
Row 24 7/25/2011 4:22:15 PM AB Order back panel

Fixed in the upper section are six rows with the codes

Row 11 AB
Row 12 GH
Row 13 KM
Row 14 TR
Row 15 PO
Row 16 WO

Therefore, there could be many dates with the same code i.e. code AB as noted above but we only want the highest data entered i.e. 7/27/2011 3;18:24 PM AB Setup booking. That info for Code AB would end up in Row 11 with the Date in Cell L11 and the Description in Cell N11 and the information for code GH would end up in cells L12 and N12 etc.

What I need to happen is that the formula goes and finds the highest date for each code and places that information into Rows 11 through Row 16 and get the date and description to match. Sometimes we may only use one or two codes and the rows in the lower section could be several hundred. I am at work right now and cannot insert the HTML for the examples as our IT does not allow such things. Hopefully this makes sense.

 

[mvptomlinson]

Hi Papi,

To get the highest date for each code:

Assuming A11:A16 contain the codes you specified, and your other data is laid out as Dates in A21:A100, Codes in B21:B100 and Descriptions in C21:C100, use the array formula (confirmed with CTRL+SHIFT+ENTER, not just ENTER):

=MAX(IF($B$21:$B$100=$A11,$A$21:$A$100))

To get the corresponding Description use the array formula:

=INDEX($C$21:$C$100,MATCH(A11&INT(C11),$B$21:$B$100&INT($A$21:$A$100),0))

 

[Papi]

Hello mvptomlinson,

Thanks so much. The first part works fine but I am not clear on the second array formula as it gived me anerror. I tried several work arounds but cannot seem to get it to work even entering the CTRL+SHIFT+Enter as the functions are new to me.

 

[mvptomlinson]

Please post a sample workbook at one of the sites in my signature and post a link to the file here.

 

[Papi]

I will have to do that tomorrow as the file is at work. Thanks for your patience and help.

 

[Papi]

Hello mvptomlinson,

I created a modified version at home and figured out what was needed. Again, thank you for your help as this moves me ahead a long way with your solutions.