Result for Last Occurence

**Greg777** · 08-10-2011, 01:37 PM

Hello Excel Forum,

I've attached an Excel2010 doc with a few columns. I'm trying to find the most recent occurance of a number and the date that corresponds to it.

Column B counts the number of times that a value repeats in column A. Column C is the information that I am wanting to reference.

I'm like to use VLOOKUP() or OFFSET()&MATCH() to reference these dates.

How would I best do this? Thanks for any help!

Greg

**JBeaucaire** · 08-10-2011, 02:12 PM

Reference them in what way? I don't see a manually mocked up representation of what your desired results would be.

**NBVC** · 08-10-2011, 02:13 PM

Try:

=LOOKUP(2,1/($A$2:$A$5=2),$C$2:$C$5)

replace 2 with number your are looking for or replace with a cell reference.

**Greg777** · 08-10-2011, 02:53 PM

Thanks for the request for clarification. I've attached a new doc with more info and an attempt from what was posted.

Data table is shown in columns A - C.

I'm wanting to calculate two columns based upon an input column E. I'm able to calculate the resulting date for the first occurance, but have not been able to figure out how to do so for the last occurance.

It would be OK to use a helper columnif required. I was thinking that some combination of Rank(), Max() may be useful. If a variation on the proposed Vlookup() function will work I'm not able to figure it out.

A mock-up of the desired results is in column H.

Thanks for your help!

Greg

**Greg777** · 08-10-2011, 02:55 PM

attachment for previous

**NBVC** · 08-10-2011, 03:12 PM

Try:

=IFERROR(LOOKUP(2,1/($A$2:$A$10=E2),$C$2:$C$10),"")

copied down

**Greg777** · 08-10-2011, 03:30 PM

Works perfectly. Thanks again for your help. I've attached a completed workbook with the results in column G (in orange) for others' benefit.

**Haseeb A** · 08-10-2011, 04:06 PM

Also,

=IFERROR(LOOKUP(2,MATCH($A$2:$A$10,E2,0),$C$2:$C$10),"")

I think this would avoid dividing array like 1/{TRUE,TRUE,FALSE...} & could be more faster...