Find Last Occurrence in List

Thanks:  0
Likes:  0

Thread: Find Last Occurrence in List

1. Find Last Occurrence in List

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

2. Re: Find Last Occurrence in List

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

3. Re: Find Last Occurrence in List

Try this

 =LOOKUP(2,1/(B100:B109=1),A100:A109)

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

4. Re: Find Last Occurrence in List

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

Much Kudos!

5. Re: Find Last Occurrence in List

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

6. Re: Find Last Occurrence in List

You're looking at it. MrExcel.Com.

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

7. Re: Find Last Occurrence in List

This array formula should work nice too

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

8. Re: Find Last Occurrence in List

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

In the very last column I have the following formula:
=INDEX(\$A\$2,MAX(IF(\$N\$2:\$N\$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

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•