Results 1 to 4 of 4

Thread: Lookup a value from a range of dates in excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup a value from a range of dates in excel

    I have two tables. A list of accounts and the other is a change log. I need to add a new column in table 1 where the value is the amount in table 2 for the correct account and correct validity period.

    Please help

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Lookup a value from a range of dates in excel

    Example:

    Excel 2010
    ABCD
    1AccountStartEndAmount
    21January 1, 2012January 31, 201230
    32January 12, 2012February 12, 2012#N/A
    4
    5AccountAmountStartEnd
    6110January 1, 2009December 5, 2010
    7120December 6, 2010June 1, 2011
    8130June 2, 2011December 1, 2012
    9213January 15, 2011December 15, 2011
    10220December 16, 2011February 10, 2012

    Sheet1



    Worksheet Formulas
    CellFormula
    D2=INDEX(B$6:B$10,MATCH(1,INDEX((A$6:A$10=A2)*(C$6:C$10<=B2)*(D$6:D$10>=C2),),FALSE))
    D3=INDEX(B$6:B$10,MATCH(1,INDEX((A$6:A$10=A3)*(C$6:C$10<=B3)*(D$6:D$10>=C3),),FALSE))

    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    Jun 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a value from a range of dates in excel

    Hi Andrew,

    Thanks for this. Not sure why I can't match some of the formula...date is within the correct range..may i know what 1 in (match(1, index...) means? thanks.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Lookup a value from a range of dates in excel

    Can you give some examples that don't work as expected? Multiplying booleans returns 1 or 0. Match returns the relative position of the first 1.
    Microsoft MVP - Excel

Some videos you may like

User Tag List

Tags for this Thread

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
  •