Find last occurrence

[rfinnegan]

Hi All:

I'm trying to track delivery driver performance and want to see of the 5 trucks that left the yard last today, when was the last time they left last. I'm not concerned with how frequently they were last, just the last time.

The data I have lists the truck numbers (1-26) in columns B2-F82. The date of occurrence is in column G. I wanted the date of last occurrence for the truck in column B to populate in column H. C in I, D in J, etc.

I can do a vlookup on the truck number in B2 and have it return the date in H2, but can't do so for the trucks in C2-F2. (I'm running the vlookup un the data from B3:F82.

I'm stumped. I tried SUMIF, but that didn't work either. I though this would be a simple vlookup, but it isn't. Or I've missed something simple.

Thanks in advance

 

[Aladin Akyurek]

Try to create a scaled-down sample, post it here along with the desired result.

 

[rfinnegan]

Try to create a scaled-down sample, post it here along with the desired result.

First, sorry I have it posted as text and use _ and ... to seperate values, but the last 2 times I tried using the suggested Add-in, I ended up with oddball errors elsewhere in Excel.

That said, here's where I am and where I'm trying to go -
On the left are my "Truck Stragglers". These are the last 5 trucks to leave the yard on a given day. I've given them the headers of A-E.
It shows the truck number (A-E) and the date. So on Nov 30 the last 5 trucks to leave were 3,12,14,10,17.
On the right side is what I'm trying to calculate which is the date of the trucks last "last 5" occurrence. Looking at the trucks mentioned above, truck 3 was one of the last 5 trucks on Nov 29. Truck 12 was also one of the last 5 trucks on Nov 29 also.
Trucks 14,10,17 don't have a prior occurrence in this data.

Then looking at Nov 29 data I can see truck 24 had a prior occurrence on Nov 26. Etc.

So in summary, I have the data on the left (A-E + the date) and I'm trying to calculate the data/Date on the right (A-E)

Truck Stragglers Date Prior Occurrence
A____B____C____D____E...................... A________B________C________D_____E

3____12____4___10___17___30-Nov.......29-Nov____29-Nov___n/a_______n/a___n/a
24___11___15___12___3____29-Nov.......26-Nov____27-Nov___28-Nov____n/a___n/a
2_____9___15___22___21___28-Nov........n/a______n/a______26-Nov____n/a___n/a
1____20___18___11___5____27-Nov........n/a______26-Nov____n/a______n/a___26-Nov
4____5____24___15___20___26-Nov........n/a______n/a______25-Nov____n/a_____n/a
16___19___24___8____6____25-Nov


Again, any help is appreciated.

 

[rfinnegan]

First, sorry I have it posted as text and use _ and ... to seperate values, but the last 2 times I tried using the suggested Add-in, I ended up with oddball errors elsewhere in Excel.

That said, here's where I am and where I'm trying to go -
On the left are my "Truck Stragglers". These are the last 5 trucks to leave the yard on a given day. I've given them the headers of A-E.
It shows the truck number (A-E) and the date. So on Nov 30 the last 5 trucks to leave were 3,12,14,10,17.
On the right side is what I'm trying to calculate which is the date of the trucks last "last 5" occurrence. Looking at the trucks mentioned above, truck 3 was one of the last 5 trucks on Nov 29. Truck 12 was also one of the last 5 trucks on Nov 29 also.
Trucks 14,10,17 don't have a prior occurrence in this data.

Then looking at Nov 29 data I can see truck 24 had a prior occurrence on Nov 26. Etc.

So in summary, I have the data on the left (A-E + the date) and I'm trying to calculate the data/Date on the right (A-E)

Truck Stragglers Date Prior Occurrence
A____B____C____D____E...................... A________B________C________D_____E

3____12____4___10___17___30-Nov.......29-Nov____29-Nov___n/a_______n/a___n/a
24___11___15___12___3____29-Nov.......26-Nov____27-Nov___28-Nov____n/a___n/a
2_____9___15___22___21___28-Nov........n/a______n/a______26-Nov____n/a___n/a
1____20___18___11___5____27-Nov........n/a______26-Nov____n/a______n/a___26-Nov
4____5____24___15___20___26-Nov........n/a______n/a______25-Nov____n/a_____n/a
16___19___24___8____6____25-Nov


Again, any help is appreciated.

Here's what I eventually worked out (I also moved the data into different columns to accommodate some other data.)
{=INDEX($T3:$T$200,SMALL(IF($M3:$Q$200=M2,ROW($T3:$T$200)-ROW(T3)+1),1))}