• 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...

  • 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

R

rajkenya1

Member
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
 

Attachments

  • Date Range Example.xlsx
    9.5 KB · Views: 8
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.
 

Attachments

  • Copy of Date Range Example.xlsx
    10.6 KB · Views: 5
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
 
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.
 
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
 

Attachments

  • Date Range Example.xlsx
    9.5 KB · Views: 6
rajkenya1 said:
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
Click to expand...

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
 
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)
 
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
 
Both formulas work brilliantly. Thank you so much for understanding my query and also resolving it for me. Thank you. Kind regards. Much appreciated.
 
You must log in or register to reply here.
Back
Top