Formula for finding last incident?

[AnthonyMoody]

Hey!
I want to find a formula that do the trick to find the last incident of a value or text. Lets say we have five different salers, and want a column where the last value or text is shown.

So the formula should search in the column "SALER" and the last date with this saler and show the result in a cell.

I have made a sheet where I try to show you the problem. In the "LAST SALE" column the value of last sale for the saler should be. And the search for it should be based on the names and dates in column A and B.

Hope you understand the problem.

Thanks in advance!
Anthony Moody

[arlu1201]

I think a code should do this for u. But in what format do you need the output?

[tigeravatar]

AnthonyMoody,

Attached is a modified version of your example workbook. In cell D2 it contains the following array formula:
=SUMPRODUCT(--($B$2:$B$204=B2),--($A$2:$A$204=MAX(IF($B$2:$B$204=B2,$A$2:$A$204,0))),$C$2:$C$204)

And then its just copied down.

An array formula needs to be entered with Ctrl+Shift+Enter and not just enter. Ctrl+Shift+Enter will need to be used if formula is altered as well.

Hope that helps,
~tigeravatar

[zbor]

Try this:

=LOOKUP(2, 1/(B:B=B2), C:C)

[AnthonyMoody]

okey! maybe I did not explain good enough.... What I want is that a formula is searching for the salers last sale value.... based on the salers name and the date of the sale...


I want to find the last date of sale value based on the saler!

I working with this formulas at the time...
INDEX
MATCH
LARGE or MAX?
IF

I wonder how I can make this a formula.. in cell C14 =INDEX($c2$:$c13(IF(MATCH($b14;$b$2:$b13;0);LARGE($a$2:$a13;1)

Hope this cleared it a little bit!

[dangelor]

Have you tried using a Pivot Table? (See attached)

[dangelor]

Here's the array formula (Ctlr+Shft+Enter) used in the attached file.

=INDEX($A$2:$A$204,LARGE(IF($B$2:$B$204=$E2,ROW($A$2:$A$204)-ROW($A$2)+1),1))