+ Reply to Thread
Results 1 to 7 of 7

Lookup up latest date record in a list

  1. #1
    Registered User
    Join Date
    01-22-2011
    Location
    Aylesbury, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Lookup up latest date record in a list

    i have a list with multiple entries for customers recording information on visits made by our salesmen.

    How do I look up a particular column within the latest date record please?
    Last edited by tonyp17; 09-27-2011 at 10:54 AM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Lookup up latest date record in a list

    Is the data listed in date order - if so is it with the latest dates at the top or the other way round?
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-22-2011
    Location
    Aylesbury, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Lookup up latest date record in a list

    The list is in date order with the latest date at the bottom.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Lookup up latest date record in a list

    Assuming a customer "Joe", get the latest date for Joe with this formula

    =LOOKUP(2,1/(A$2:A$100="Joe"),B$2:B$100)

    assuming customers in column A and dates in B.

    If you want the data from another column just use that in place of B, i.e. for column C data for the latest visit to Joe

    =LOOKUP(2,1/(A$2:A$100="Joe"),C$2:C$100)

  5. #5
    Registered User
    Join Date
    01-22-2011
    Location
    Aylesbury, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Smile Re: Lookup up latest date record in a list

    Thank you daddylonglegs. Your formula works but I do not understand how it works.

    I use VLOOKUP all the time but the syntax here is different. Can you point me to where the syntax you have used is explained please?

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Lookup up latest date record in a list

    It's not something you'll see in Excel help - see here for an explanation from an expert....

    It's a little complex, though....

  7. #7
    Registered User
    Join Date
    01-22-2011
    Location
    Aylesbury, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Smile Re: Lookup up latest date record in a list

    Thank you again.

    My word it is complex. I will stick with trying to use the syntax without understanding fully why it works.

    For such a relatively common requirement Microsoft should create a function that can be used.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1