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

Lookup Multiple Dates in a List of Dates?

Gregg Wolin

Member
I am trying to eliminate the need for helper columns while tracking lease rates based on a schedule of dates.
Each lease period has a beginning date, an end date and a lease rate. I'd like to create a single column array that looks at a dynamic spill-range of dates, and returns the corresponding lease rate associated each date. The attached workbook better illustrates what i'm talking about. Any suggestions would be greatly apprciated.
 

Attachments

  • chandoo-lease dates.xlsx
    16 KB · Views: 4
Gregg
In the attached file, you will find a power query solution that matches your expected results. You will need to build two queries and then join them in a left inner join to get the final answer
 

Attachments

  • chandoo-lease dates.xlsx
    31.5 KB · Views: 3
Does this work?
Code:
=XLOOKUP(G4#,Table1[Start],Table1[$/SF],,-1,2)
I've been experimenting with permutations of this formula and came across another wrinkle:

Is it possible to have the SEQUENCE function triggered by embedded in an "If-Then" statement?
Basically, I have a list of dates and I'd like to I'd like to start counting (from 1) once a certain date is reached. I uploaded a revised workbook.
 

Attachments

  • chandoo-lease dates.xlsx
    28.9 KB · Views: 2
Not sure I got it right this time, but are you looking for:
Code:
=LET(Dates,FILTER(G4#,G4#>=$B$6),Seq,SEQUENCE(COUNT(Dates),,1,1),CHOOSE({1\2},Dates,Seq))

79216
 

Attachments

  • chandoo-lease dates(1).xlsx
    30.2 KB · Views: 2
Not sure I got it right this time, but are you looking for:
Code:
=LET(Dates,FILTER(G4#,G4#>=$B$6),Seq,SEQUENCE(COUNT(Dates),,1,1),CHOOSE({1\2},Dates,Seq))

View attachment 79216

It works (thanks)!
That said, I feel like it shouldn't be that complicated to have a counter array that "starts" once a neighboring array reaches a certain value.
Ideally, the cells in the Holding Dates array would have zeros until it reached the matching Master Date as illustrated on the attached revised workbook.
 

Attachments

  • chandoo-lease dates_v3.xlsx
    29.3 KB · Views: 3
Hmm, maybe if it were "not complicated", you'd not need to ask? ;)
That said, this seems to be what you are after. A shorter, faster, better solution might be possible, I just don't know it yet.
Code:
=LET(
Mdtes;EDATE(B5;SEQUENCE(D5;1;0));
IsEarly;Mdtes<$B$6;
IsLate;Mdtes>$C$6;
IsHper;(IsEarly+IsLate)=0;
MSeq;SEQUENCE(COUNT(Mdtes);;1;1);
HDtes;IF(IsHper;Mdtes;0);
SkipEarly;SUM(IF(IsEarly;1;0));
Hper;IFS(IsEarly;0;IsLate;0;TRUE;MSeq-SkipEarly);
CHOOSE({1\2\3};Mdtes;Hper;HDtes))
 

Attachments

  • chandoo-lease dates_v3.xlsx
    25.2 KB · Views: 4
Hmm, maybe if it were "not complicated", you'd not need to ask? ;)
That said, this seems to be what you are after. A shorter, faster, better solution might be possible, I just don't know it yet.
Code:
=LET(
Mdtes;EDATE(B5;SEQUENCE(D5;1;0));
IsEarly;Mdtes<$B$6;
IsLate;Mdtes>$C$6;
IsHper;(IsEarly+IsLate)=0;
MSeq;SEQUENCE(COUNT(Mdtes);;1;1);
HDtes;IF(IsHper;Mdtes;0);
SkipEarly;SUM(IF(IsEarly;1;0));
Hper;IFS(IsEarly;0;IsLate;0;TRUE;MSeq-SkipEarly);
CHOOSE({1\2\3};Mdtes;Hper;HDtes))
Thank you!!!!!!! This is going to take me some time to unpack as my knowledge of the LET function is limited.
 
Gregg, I understand what you are saying, but if you have a bit of time.....

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
Thank you!!!!!!! This is going to take me some time to unpack as my knowledge of the LET function is limited.
See every argument as a helper calculation or helper column.
You can also add a name like "result" to the last argument (before choose). Then before the last ")" add ";result" without the quotation marks.
Then go through the list of arguments and replace the final "result" with the intermediate calculations. So you discover the logic.
 
Back
Top