Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Lookup value /="" and closest to a given date

This is a discussion on Lookup value /="" and closest to a given date within the Excel Questions forums, part of the Question Forums category; Hi, I have a table. Dates in Column A, values in Column B. I need to lookup the last data ...

  1. #1
    Board Regular
    Join Date
    Apr 2008
    Posts
    83

    Default Lookup value /="" and closest to a given date

    Hi,

    I have a table. Dates in Column A, values in Column B. I need to lookup the last data from column B for a date given but only if the value is not "" (I populate column B with a formula, and if that formula doesn't find anything, it returns a ""). If there is no data in Column B for the date given, I need the last data which is not "", closest to but less than the date given.

    I'm putting this into a cell with a multilayered conditional formula, so I'm just curious what would be the shortest solution to keep the already complex formula as simple as possible.

    best,
    deL

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,682

    Default Re: Lookup value /="" and closest to a given date

    Hi deL

    For ex., with you date list in B2:B20 and the input date in D2, in E2:

    =MAX(IF($B$2:$B$21<=D2,$B$2:$B$21))

    This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

    If no data exists less than the input data the result is 0, so format the result cell with a message, for ex.:

    yyyy-mm-dd;;"N/A"


     ABCDEF
    1      
    2 2012-09-07 2012-11-012012-10-17 
    3 2012-10-08    
    4 2012-09-21    
    5      
    6 2012-10-09    
    7 2012-09-19    
    8 2012-09-22    
    9 2012-12-14    
    10 2012-09-13    
    11 2012-09-27    
    12      
    13      
    14 2012-11-17    
    15 2012-12-06    
    16 2012-10-17    
    17 2012-12-09    
    18 2012-11-17    
    19      
    20 2012-12-09    
    21      
    [Book1]Sheet1
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    Board Regular
    Join Date
    Apr 2008
    Posts
    83

    Default Re: Lookup value /="" and closest to a given date

    Either I don't get what you wrote, or I should've been more clear. So just to clarify:

    If I give 07/01/2012, then I need the value 229.13 returned. If, however I gave 06/26/2012, I need 229.72 to be returned. The Value column is populated with and INDEX/MATCH lookup formula with an IFERROR to give "" if the INDEX/MATCH formula gives an error (I'm not sure if it's important to know, I just thought it wouldn't hurt).

    Date Value
    06/25/2012 229.97
    06/26/2012 229.72
    06/27/2012 228.97
    06/28/2012 231.62
    06/29/2012 229.13
    06/30/2012
    07/01/2012
    07/02/2012 225.91
    Last edited by deLockloire; Sep 2nd, 2012 at 05:05 AM.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,682

    Default Re: Lookup value /="" and closest to a given date

    Quote Originally Posted by deLockloire View Post
    Either I don't get what you wrote, or I should've been more clear.
    A table always helps ... but I should have read your post with more attention.

    See if this is more like it.

    In E2:

    =IFERROR(LOOKUP(2,1/($A$2:$A$20<=D2)/(B2:B20<>""),$B$2:$B$20),"N/A")


     ABCDEF
    1      
    22012-09-071 2012-09-1911 
    32012-09-082    
    42012-09-093    
    52012-09-104    
    62012-09-115    
    72012-09-12     
    82012-09-13     
    92012-09-14     
    102012-09-159    
    112012-09-1610    
    122012-09-1711    
    132012-09-18     
    142012-09-19     
    152012-09-2014    
    162012-09-2115    
    172012-09-2216    
    182012-09-2317    
    192012-09-2418    
    202012-09-2519    
    21      
    [Book1]Sheet1
    Last edited by pgc01; Sep 2nd, 2012 at 05:14 AM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    Board Regular
    Join Date
    Apr 2008
    Posts
    83

    Default Re: Lookup value /="" and closest to a given date

    Hi,

    This one works and does exactly what I need. Thanks.

    best,
    deL

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,682

    Default Re: Lookup value /="" and closest to a given date

    I'm glad it helped. Thanks for the feedback.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,244

    Default Re: Lookup value /="" and closest to a given date

    Quote Originally Posted by deLockloire View Post
    Either I don't get what you wrote, or I should've been more clear. So just to clarify:

    If I give 07/01/2012, then I need the value 229.13 returned. If, however I gave 06/26/2012, I need 229.72 to be returned. The Value column is populated with and INDEX/MATCH lookup formula with an IFERROR to give "" if the INDEX/MATCH formula gives an error (I'm not sure if it's important to know, I just thought it wouldn't hurt).

    [...]
    What would be the outcome you want to see if we had:
    Date Value
    6/25/2012 229.97
    6/26/2012 229.72
    6/27/2012 228.97
    6/28/2012 231.62
    6/29/2012 229.13
    6/29/2012 240.75
    6/30/2012
    7/1/2012
    7/2/2012 225.91

    for 7/1/2012: 229.13 or 240.75? The latter will be calculated by the formula PGC suggested.
    Last edited by Aladin Akyurek; Sep 2nd, 2012 at 06:25 AM.
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    Board Regular
    Join Date
    Apr 2008
    Posts
    83

    Default Re: Lookup value /="" and closest to a given date

    Hi,

    Fortunately, there is only one value for each day in this table, so the formula suggested works well.
    But just for curiosity's sake: why do you ask?

    best,
    deL

    Quote Originally Posted by Aladin Akyurek View Post
    What would be the outcome you want to see if we had:
    Date Value
    6/25/2012 229.97
    6/26/2012 229.72
    6/27/2012 228.97
    6/28/2012 231.62
    6/29/2012 229.13
    6/29/2012 240.75
    6/30/2012
    7/1/2012
    7/2/2012 225.91

    for 7/1/2012: 229.13 or 240.75? The latter will be calculated by the formula PGC suggested.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,244

    Default Re: Lookup value /="" and closest to a given date

    Quote Originally Posted by deLockloire View Post
    Hi,

    Fortunately, there is only one value for each day in this table, so the formula suggested works well.
    But just for curiosity's sake: why do you ask?

    best,
    deL
    If multiple values for the same date would occur and you'd need the first of such values, a different formula would be required. Curious about that formula too?
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    Board Regular
    Join Date
    Apr 2008
    Posts
    83

    Default Re: Lookup value /="" and closest to a given date

    Quote Originally Posted by Aladin Akyurek View Post
    If multiple values for the same date would occur and you'd need the first of such values, a different formula would be required. Curious about that formula too?
    You bet.

Page 1 of 2 12 LastLast

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