Results 1 to 8 of 8

Find Last Occurrence in List

This is a discussion on Find Last Occurrence in List within the Excel Questions forums, part of the Question Forums category; Peeps... I have the following data... Code: A B 100 01-Jan 0 101 02-Jan 0 102 03-Jan 1 103 04-Jan ...

  1. #1
    Board Regular
    Join Date
    Jul 2006
    Posts
    132

    Default 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. #2
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,740

    Default 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
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    30,414

    Default 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..
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    Board Regular
    Join Date
    Jul 2006
    Posts
    132

    Default Re: Find Last Occurrence in List

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

    Much Kudos!

  5. #5
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default 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. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    30,414

    Default 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.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    New Member Quasi's Avatar
    Join Date
    Jun 2008
    Location
    Poland
    Posts
    36

    Default 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),"")))

    Last edited by Quasi; Jun 25th, 2008 at 01:26 PM.

  8. #8
    New Member
    Join Date
    Apr 2014
    Posts
    5

    Default 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com