# Find Last Occurrence in List

#### deepheat101

##### Board Regular
Peeps...

I have the following data...

Code:
``````       A     B
100  01-Jan  0
101  02-Jan  0
102  03-Jan  1
103  04-Jan  0
104  05-Jan  0
105  06-Jan  2
106  07-Jan  0
107  08-Jan  0
108  09-Jan  1
109  10-Jan  0``````

Considering that the list will be added to each day, how do I go about finding date of the last occurrence of any given number in column B. For example the last 0 would return "10-Jan"; the last 1 would return "09-Jan"; the last 2 would "06-Jan". I had been working with VLookUp before quickly realizing that I need a VLookDown...

Cheers

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Oaktree

##### MrExcel MVP
Try:

=LOOKUP(2,1/(B1:B10=1),A1:A10) for the last instance of 1 or

=LOOKUP(2,1/(B1:B10=0),A1:A10) for the last instance of 0

#### Jonmo1

##### MrExcel MVP
Try this

<TABLE style="WIDTH: 565pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=753 border=0 x:str><COLGROUP><COL style="WIDTH: 565pt; mso-width-source: userset; mso-width-alt: 27538" width=753><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 565pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=753 height=17>=LOOKUP(2,1/(B100:B109=1),A100:A109)</TD></TR></TBODY></TABLE>

Bold Red 1 is the part to adjust for either 0 1 or 2..
You can change that to a cell reference that contains a 0 1 or 2..

Hope that helps..

#### deepheat101

##### Board Regular
Many Thanks for your very speedy (and very correct) responses..

Much Kudos!

#### DonkeyOte

##### MrExcel MVP
Oaktree / jonmo...

is there a good resource for learning the extended *possibilities* of LOOKUP ?

I've seen a couple today and am intrigued as to how they work... Aladin provided the other.

I've read through the standard XL help which taught me the basics -- was hoping you may know of a good resource site to look at ??

#### Jonmo1

##### MrExcel MVP
You're looking at it. MrExcel.Com.

Everything I have learned (beyond your basic Vlookup and using the macro recorder) I learned here.

#### Quasi

##### New Member
This array formula should work nice too

=INDEX(\$B\$1:\$B\$10,MAX(IF(\$C\$1:\$C\$10=E1,ROW(\$C\$1:\$C\$10),"")))

Last edited:

#### jellybeank28

##### New Member
Quasi

Any idea what formula I can use for the following?

 Quoted Part Quote # RFQ Date Sales Order Part Job/SO Order Date Last Date purchased 202657 - PIN ASSEMBLY 4448 4/14/04 497474-3 CUST NO. MRX00003517 3928-001 4/16/04 #VALUE! 4487 6/10/04 MC114N CUST NO. MRX00003783 3928-003 4/16/04 04/16/04 801963 REV A 4671 4/7/05 ER412-12A CUST NO MRX00031771 3928-006 4/16/04 #N/A 801964 REV A 4671 4/7/05 030-2409-001 CUST P/N 01308805 3927 4/20/04 #N/A 204795 REV - 4671 4/7/05 7889-1505 3929 4/21/04 #N/A 204562 REV C 4671 4/7/05 801870D 3926 4/22/04 #N/A 25M0224 4828 3/1/06 9609-0090 3932 4/28/04 #N/A 9645-0097 REV A 4537 3/13/06 9643-0090 3933 4/28/04 04/28/04

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

In the very last column I have the following formula:
=INDEX(\$A\$2,MAX(IF(\$N\$2:\$N\$7000<D2,ROW(\$J\$2:\$J\$7000),"")))
But as you can see in the 2nd row its feedback 4/16/04 instead of a date that is greater than 6/10/04 (3rd column). All I want is for the spreadsheet to look at the "Quoted Part" and find the most recent date (later than the "RFQ Date" that it was ordered based on "Sales Order Part" and "Order Date" columns. In the end I only need to see the last date it was ordered in the very last column.

Any help would be much appreciated!
Kelly

Replies
0
Views
75
Replies
2
Views
117
Replies
3
Views
41
Replies
4
Views
175
Replies
3
Views
81

1,191,690
Messages
5,988,107
Members
440,126
Latest member
duque00

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