+ Reply to Thread
Results 1 to 12 of 12

[Solved] Formula to LookUp most Recent Information

  1. 09-24-2015, 04:37 AM #1
    lsirobinson
    lsirobinson is offline
    Registered User
    Join Date
    12-02-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    7

    [Solved] Formula to LookUp most Recent Information

    Hi,

    I wonder if someone can help me...please.

    How would you do the following

    I want to populate column B in a worksheet with the current status of a lead from another worksheet containing the information by looking up the Lead ID in Column A and getting the data from Column B however the lead ID could be in the information worksheet multiple times and I want to get the most recent information. Column C in the worksheet contains the date and time the lead status was changed. It is also updated in real time so will change constantly. Can it be done?

    First Worksheet looks like this:

    Lead ID Current Status
    99899483
    99899423
    99897194
    99896768
    99896519
    99896448
    99895034
    99894700

    The information looks like this:
    Lead ID Status Date & Time Sold
    99899483 Quoted 22/09/2015 11:18
    99899423 Quoted 22/09/2015 11:15
    99897194 Valid 22/09/2015 11:06
    99896768 Declined 22/09/2015 11:05
    99896519 Valid 22/09/2015 11:05
    99896448 Valid 22/09/2015 11:05
    99895034 Do Not Call 22/09/2015 11:02
    99894700 Valid 22/09/2015 11:01
    99894014 Valid 22/09/2015 11:00
    99892630 Valid 22/09/2015 10:57
    99891755 Quoted 22/09/2015 10:53
    99889639 Valid 22/09/2015 08:49
    99889126 Can't Beat Quote 22/09/2015 07:50
    99888793 Valid 22/09/2015 03:27
    99888552 Valid 22/09/2015 00:36
    99886554 No Number 21/09/2015 19:43
    99886407 No Number 21/09/2015 19:27
    99883213 Valid 21/09/2015 15:48
    99881028 Valid 21/09/2015 14:04
    99881009 Valid 21/09/2015 14:03
    99880854 Invalid 21/09/2015 13:54
    99880717 Valid 21/09/2015 13:48
    99880499 Invalid 21/09/2015 13:45
    99880488 Quoted 21/09/2015 13:45
    99879493 Quoted 21/09/2015 12:54
    99878473 Valid 21/09/2015 12:06
    99878088 Valid 21/09/2015 11:43
    99877872 Valid 21/09/2015 11:29


    Thanks in advance for your help.
    Louise
    Last edited by lsirobinson; 09-24-2015 at 11:11 AM.
    Reply With QuoteRegister To Reply
  2. 09-24-2015, 05:00 AM #2
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to LookUp most Recent Information

    Hi,

    a first approach

    in A2 first Lead ID, database in Sheet2, in column c dates-times.


    =MAX((Sheet2!A$2:A$1000=A2)*Sheet2!C$2:C$1000,)

    Hope it helps
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  3. 09-24-2015, 10:25 AM #3
    lsirobinson
    lsirobinson is offline
    Registered User
    Join Date
    12-02-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula to LookUp most Recent Information

    Thank you for your response. This brings the date and time however I would like to get the current status information next to the most recent date and time from column B. Is this possible?

    Thanks Again
    Louise
    Reply With QuoteRegister To Reply
  4. 09-24-2015, 10:30 AM #4
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to LookUp most Recent Information

    Hi,

    in column B status


    =INDEX(Sheet2!B$2:B$1000,MATCH(A2&MAX(INDEX((Sheet2!A$2:A$1000=A2)*Sheet2!C$2:C$1000,)),INDEX(Sheet2!A$2:A$1000&Sheet2!C$2:C$1000,),0))



    Hope that helps

    Edit: a INDEX was missing in my previous formulas. Sorry: problems in traslating from Italian settings
    Last edited by canapone; 09-24-2015 at 10:42 AM.
    Reply With QuoteRegister To Reply
  5. 09-24-2015, 10:43 AM #5
    lsirobinson
    lsirobinson is offline
    Registered User
    Join Date
    12-02-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula to LookUp most Recent Information

    Thank you. This is bringing the data but not the most recent date......Sorry I don't know if I am doing something wrong.

    Thanks
    Louise
    Reply With QuoteRegister To Reply
  6. 09-24-2015, 10:46 AM #6
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to LookUp most Recent Information

    Hi,

    can you upload a small example? Probably I've been not very clear and I've corrected the formula (a INDEX was wissing in action) : not your fault.
    Last edited by canapone; 09-24-2015 at 10:52 AM.
    Reply With QuoteRegister To Reply
  7. 09-24-2015, 10:52 AM #7
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to LookUp most Recent Information

    Hi,

    a small example attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 09-24-2015, 10:54 AM #8
    lsirobinson
    lsirobinson is offline
    Registered User
    Join Date
    12-02-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula to LookUp most Recent Information

    This is the formula result.

    Lead ID Current Status
    99899483 Quoted
    99899423 Quoted
    99897194 Valid
    99896768 Declined
    99896519 Valid
    99896448 Valid
    99895034 Do Not Call
    99894700 Valid
    99894014 Valid
    99892630 Valid
    99891755 Quoted
    99889639 Valid
    99889126 Can't Beat Quote
    99888793 Valid
    99888552 Valid
    99886554 No Number
    99886407 No Number
    99883213 Valid
    99881028 Valid
    99881009 Valid
    99880854 Invalid
    99880717 Valid
    99880499 Invalid
    99880488 Quoted
    99879493 Quoted
    99878473 Valid
    99878088 Valid
    99877872 Valid
    99877595 Valid
    99877543 Quoted
    99876209 Already Covered
    99874023 Invalid
    99867676 Valid
    99867674 Valid
    99867610 Quoted

    And this is the information that I am looking at.

    Lead ID Status Date & Time Sold
    99899483 Quoted 22/09/2015 11:18
    99899423 Quoted 22/09/2015 11:15
    99897194 Valid 22/09/2015 11:06
    99896768 Declined 22/09/2015 11:05
    99896519 Valid 22/09/2015 11:05
    99896448 Valid 22/09/2015 11:05
    99895034 Do Not Call 22/09/2015 11:02
    99894700 Valid 22/09/2015 11:01
    99894014 Valid 22/09/2015 11:00
    99892630 Valid 22/09/2015 10:57
    99891755 Quoted 22/09/2015 10:53
    99889639 Valid 22/09/2015 08:49
    99889126 Can't Beat Quote 22/09/2015 07:50
    99888793 Valid 22/09/2015 03:27
    99888552 Valid 22/09/2015 00:36
    99886554 No Number 21/09/2015 19:43
    99886407 No Number 21/09/2015 19:27
    99883213 Valid 21/09/2015 15:48
    99881028 Valid 21/09/2015 14:04
    99881009 Valid 21/09/2015 14:03
    99880854 Invalid 21/09/2015 13:54
    99880717 Valid 21/09/2015 13:48
    99880499 Invalid 21/09/2015 13:45
    99880488 Quoted 21/09/2015 13:45
    99879493 Quoted 21/09/2015 12:54
    99878473 Valid 21/09/2015 12:06
    99878088 Valid 21/09/2015 11:43
    99877872 Valid 21/09/2015 11:29
    99877595 Valid 21/09/2015 11:18
    99877543 Quoted 21/09/2015 11:16
    99876209 Already Covered 21/09/2015 10:33
    99874023 Invalid 21/09/2015 10:21
    99867676 Valid 21/09/2015 08:09
    99867674 Valid 21/09/2015 08:09
    99867610 Quoted 21/09/2015 07:53
    99899483 Can't Beat Quote 22/09/2015 07:53
    99899423 Valid 23/09/2015 07:53
    99897194 Valid 24/09/2015 07:53
    99896768 No Number 25/09/2015 07:53
    99896519 No Number 26/09/2015 07:53
    99896448 Valid 27/09/2015 07:53
    99895034 Valid 28/09/2015 07:53
    99894700 Valid 29/09/2015 07:53
    99894014 Invalid 30/09/2015 07:53
    99892630 Valid 01/10/2015 07:53
    99891755 Invalid 02/10/2015 07:53
    99889639 Quoted 03/10/2015 07:53
    99889126 Quoted 04/10/2015 07:53
    99888793 Valid 05/10/2015 07:53
    99888552 Invalid 06/10/2015 07:53
    99886554 Valid 07/10/2015 07:53
    99886407 Invalid 08/10/2015 07:53


    Does this help you?
    Reply With QuoteRegister To Reply
  9. 09-24-2015, 11:00 AM #9
    lsirobinson
    lsirobinson is offline
    Registered User
    Join Date
    12-02-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula to LookUp most Recent Information

    As an example Lead ID 99891755 has the current status valid on 02/10/2015 but it had an earlier status of Quoted on 22/09/2015 I want to take the latest staus of Invalid however the result of the formula is quoted.

    Thanks again, hope this helps you
    Reply With QuoteRegister To Reply
  10. 09-24-2015, 11:05 AM #10
    lsirobinson
    lsirobinson is offline
    Registered User
    Join Date
    12-02-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula to LookUp most Recent Information

    Thank you so much. This is exactly what I needed.
    Reply With QuoteRegister To Reply
  11. 09-24-2015, 11:10 AM #11
    lsirobinson
    lsirobinson is offline
    Registered User
    Join Date
    12-02-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    7

    [Solved] Formula to LookUp most Recent Information

    Solved query. Thank you
    Reply With QuoteRegister To Reply
  12. 09-24-2015, 11:38 AM #12
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: [Solved] Formula to LookUp most Recent Information

    Hi,

    thanks for sharing feedback.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Lookup Multiple Criteria using most recent date
    By tsadams23 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-26-2015, 09:51 AM
  2. [SOLVED] formula to lookup most recent date
    By a2424 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-23-2014, 09:22 PM
  3. [SOLVED] Lookup first two letters and return the most recent data
    By Martin Chamberlin in forum Excel General
    Replies: 12
    Last Post: 05-12-2014, 02:38 AM
  4. Lookup most recent text entry
    By BobTheRocker in forum Excel General
    Replies: 2
    Last Post: 09-28-2010, 10:16 AM
  5. Gathering information by the most recent dates
    By avidcat in forum Excel General
    Replies: 10
    Last Post: 01-20-2010, 10:17 AM
  6. Continual Updates of most recent information
    By RightRides in forum Excel General
    Replies: 17
    Last Post: 08-13-2009, 03:34 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1