Last entry in a colum that matches a certain value

**locaoimh** · 11-10-2009, 08:44 AM

Hi,

I am a newbie here so apologies if this is a stoopid question. I have searched far and wide and gotten nowhere.

I have a worksheet that looks like this...
Column A Column B
1 1/1/2009
2 2/3/2009
3 4/4/2009
1 12/5/2009
3 10/6/2009
... ...

Column A can have values 1-9 but the last entry of each number is the last time it was used (the entry in col B is the date) I want to create a summary sheet that lists 1-9 and the last date it was used next to it.

Column A Column B
1 12/5/2009
2 2/3/2009
3 10/6/2009
... ...

LOOKUP won't work because col 1 isn't sorted. I 'feel' it is possible with a combination of lookup,index or match using the Column A value in the new sheet and the colums in the original sheet, but I just can't solve it.

Any help please??

Thanks,

Liam.

**sweep** · 11-10-2009, 08:54 AM

I feel there's a simpler way, but this array (confirm with CTRL, SHIFT and ENTER) will do it:

=INDEX(B1:B20,MAX(ROW($1:$20)*(A1:A20=1)))

where you're looking for the last occurrence of 1.

Adjust ranges and the row limits to suit your data.

**martindwilson** · 11-10-2009, 08:55 AM

=MAX(IF($A$1:$A$100=3,$B$1:$B$100)) array entered with ctrl+shift+enter will do it

**DonkeyOte** · 11-10-2009, 08:56 AM

You can still use LOOKUP if you say the last entry is the last time it was used (ie data is sorted by date Ascending)

Summary
B1: =LOOKUP(2,1/(Sheet1!$A$1:$A$100=$A1),Sheet1!$B$1:$B$100)
copied down for remaining values

Other alternatives...

MAX Array (given dates)
B1: =MAX(IF(Sheet1!$A$1:$A$100=$A1,Sheet1!$B$1:$B$100))
confirmed with CTRL + SHIFT + ENTER

Pivot Table
Set Column A as Row Field and B as Data Field set to MAX.