# Find last occurrence

#### rfinnegan

##### Board Regular
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.

Last edited:

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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

#### rfinnegan

##### Board Regular
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

##### Board Regular
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))}

Replies
6
Views
267
Replies
3
Views
732
Replies
4
Views
624
Replies
3
Views
1K
Replies
4
Views
1K

1,191,196
Messages
5,985,226
Members
439,950
Latest member
Xearo96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back