Finding the latest row and getting row cell content

**rocklee** · 08-15-2010, 08:03 PM

HI folks,

I'm usually pretty resourceful but this one has me stumped a bit so here goes!

I have the following information:

Worksheet1
A B C
Date Incident No. Serial No.
1/1/2010 INC0001 SN1
1/1/2010 INC0002 SN2
2/1/2010 INC0003 SN3
2/1/2010 INC0004 SN1
11/1/2010 INC0005 SN2
12/1/2010 INC0006 SN4
13/1/2010 INC0007 SN1

Worksheet2
A B C
Serial No. Incident Date Incident No.
SN1 13/1/2010 INC0007
SN2 11/1/2010 INC0005
SN3 2/1/2010 INC0003

Basically I'm trying to get only the latest date of incidents for each serial number to be listed in Worksheet2 using Worksheet1 as the source.

To calculated my incident date in worksheet2 (B column) I have the following formula which seems to work:

{=MAX(IF(A1=worksheet1!$C$2:$C$202,worksheet1!$A$2:$A$202))}

This gets me the latest date for each serial number in worksheet 1 and paste them into worksheet2 column B.

I tried the same formula to calculate the adjacent incident number for each date and matching serial number but it gives me an error or "01/01/1900".

What I'm trying to do is once I find the latest date for each serial number, I want to retrieve information from that row on that date for that serial number and paste them into worksheet2.

**teylyn** · 08-15-2010, 08:46 PM

Hi Rocklee,

try this to retrieve the serial number

=INDEX(Worksheet1!$B$1:$B$22,MATCH(Worksheet2!B1&Worksheet2!A1,INDEX(Worksheet1!$A$1:$A$22&Worksheet 1!$C$1:$C$22,0),0))

**rocklee** · 08-15-2010, 09:44 PM

Thanks that was amazing.

Had to make a slight adjustment as there was a space in your formula but when corrected it gave me the lead to what I wanted to do.