1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return Value from Date Range in a list

Discussion in 'Ask an Excel Question' started by rajkenya1, Jun 12, 2018.

  1. rajkenya1

    rajkenya1 Member

    Messages:
    123
    Hi

    I have a range of start dates and end dates and a value associated with each period in a list of say 10 recurring periods. I want a formula to return the corresponding value if the date falls in between the range dates. For example start date 01/07/2018 and date is 30/06/2018 and if date on a row falls between these dates than return corresponding value. See attached spreadsheet as an example. Thank you

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,950
    Try........

    In F6, copied across and down :

    =IF(AND(F$2>=$B6,F$3<=$C6),$D6,"")

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    796
    Hi,
    Not sure I understand the example for 100%. It seems you increment by 2 for a yearly interval. You could use a vlookup with approximate match that looks for the end date in a range with the "max date" for each period. It does return a different result as of some point in your given range of data.

    Seeing Bosco's alternative popping up, I'm even less sure I understood the query.

    Take a peak in the attached file.

    Attached Files:

  4. rajkenya1

    rajkenya1 Member

    Messages:
    123
    Hi both. Thanks for your replies. The formula doesnt work. Basically i want cell f6-q6 to pick up for example to pick up 2 then from r6 to w4 4. Basically what it is doing is looking at the dates in rows f3 and f3, then comparing them to columns b and c and then taking the appropriate corresponding value in column d and returning it in row 6 after checking the date range
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    796
    Hi,
    I have trouble understanding what you are trying to explain. Like F3 is a cell, not a row.
    Can you highlight in the given solutions where they return an incorrect result and what the result should be.
    Please clarify what is your lookup area and what is your "working data" where the formula should be evaluated against.
  6. rajkenya1

    rajkenya1 Member

    Messages:
    123
    Hi. I am very sorry for the confusion. OK i try again.

    In the attached spreadsheet i have highlighted from cells F3 to Cell AL 3 what the results should be. So basically if you see the dates in cell F2 to AL 3 contains dates. These dates are then looked up against cells b6 to c8 and if the dates fall within the range then the value in cells f3 to al3 should show corresponding value. so if you see the date in cells r2 and r3 is now july 19 and when looking at b7 and c7 it picks up 4. I am just trying to make it flexible so incase i change dates in cell b6 to b8 it picks the correct timing of the dates and resulting values in cells f3 to al 3. I hope that is clear. Please see attached. Thanks for your help

    Attached Files:

  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,950
    In F6, copied across to AL6 :

    =IF(AND(F$2>=$B6,F$3<=$C6),$D6,IF(AND(F$2>=$B7,F$3<=$C7),$D7,IF(AND(F$2>=$B8,F$3<=$C8),$D8,"")))

    Regards
    Bosco
    Thomas Kuriakose likes this.
  8. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,913
    Just curious, what happens if there's a start date 1-Jul-18 and end date 31-Jul-19? What will be the result?

    With the current data following formula works which takes into account only start date which you can copy across.
    =LOOKUP(F2,$B$6:$B$8,$D$6:$D$8)

    Or if you need to account for both dates then you can use:
    =LOOKUP(2,1/(($B$6:$B$8<=F2)*($C$6:$C$8>=F3)),$D$6:$D$8)
    Thomas Kuriakose likes this.
  9. rajkenya1

    rajkenya1 Member

    Messages:
    123
    Well done. Thank you very much. The formula works only that a little long as i have more years to add but works well. Thank you so much for your time and effort
  10. rajkenya1

    rajkenya1 Member

    Messages:
    123
    Both formulas work brilliantly. Thank you so much for understanding my query and also resolving it for me. Thank you. Kind regards. Much appreciated.

Share This Page