• 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

AlanSidman

Well-Known Member
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

Gregg Wolin

Member
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

Gregg Wolin

Member
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

GraH - Guido

Well-Known Member
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

Gregg Wolin

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

AlanSidman

Well-Known Member
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.
 

GraH - Guido

Well-Known Member
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.
 
Top