+ Reply to Thread
Results 1 to 7 of 7

Pick the latest value using vlookup

  1. #1
    Forum Contributor
    Join Date
    08-30-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    141

    Pick the latest value using vlookup

    Hi guys.

    I want to know how should i use the vlookup (or any other suitable formula) to pick the latest value. I have two workbook. First workbook will be the master and second is the control. Let say in master wb row 2, I key in ABCD and in control wb also ABCD but in row 20 and row 24 or row 27. Later, I put the date and time in two separate columns in the control wb for ABCD in both row 20 and row 24. What i want is that the formula will pick the greatest value of the date and time between this two rows and placed it in one column in master wb. So, anyone knows how to do the vlookup for this situation?

    Thank you.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Pick the latest value using vlookup

    If you sort the control wb by date/time descending, VLOOKUP should pick to the most recent value.
    Martin

  3. #3
    Forum Contributor
    Join Date
    08-30-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: Pick the latest value using vlookup

    hi mrice. thanks for the idea but that is not what i'm looking for. the system must be automatically.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Pick the latest value using vlookup

    find the latest date using max(), then use that inside an index/match or use vlookup if the date is on the left
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    12-11-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 365
    Posts
    68

    Re: Pick the latest value using vlookup

    =MAX(IF($A$2:$A$21=J3,$B$2:$B$21)) and enter as an array formula

    in this example, my data table is A2:B21 Colum A contains the value I am looking up, column B contains a the value I want to evaluate and J3 is the value I am looking up.

    Can't seem to upload at work, so will try later.

  6. #6
    Forum Contributor
    Join Date
    08-30-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: Pick the latest value using vlookup

    Hi TheNewUnion. Thank you for the formula. It really work. But it only work for date, not time. I want it to work for both date and time. I put cell B2 13/12/2012, cell C2 9:30 AM and the next row, cell B3 12/12/2012, cell C3 4:00 PM, your formula will return the value 13/12/2012 and 4:00 PM but I want it to return the value 13/12/2012 and 9:30 AM because 4PM is greater than 9:30 AM.

  7. #7
    Forum Contributor
    Join Date
    08-30-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: Pick the latest value using vlookup

    Hi sorry. I got it. I just combine both cells (date and time) into one cell and it function well. Thank you so much

+ 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