# 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

• 16 KB Views: 4

#### 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

• 31.5 KB Views: 3

#### GraH - Guido

##### Well-Known Member
Does this work?
Code:
``=XLOOKUP(G4#,Table1[Start],Table1[\$/SF],,-1,2)``

#### Attachments

• 32 KB Views: 3

#### Gregg Wolin

##### Member
Does this work?
Code:
``=XLOOKUP(G4#,Table1[Start],Table1[\$/SF],,-1,2)``
Spectacular!

#### 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

• 28.9 KB Views: 2

#### GraH - Guido

##### Well-Known 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))``

#### Attachments

• 30.2 KB Views: 2

#### AlanSidman

##### Well-Known Member
Gregg,
Any thoughts on my suggestion in Post #2?

#### Gregg Wolin

##### Member
Gregg,
Any thoughts on my suggestion in Post #2?
Alan - I generally don't use PQ and @GraH - Guido's solution was easier for me to incorporate.

#### 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

• 29.3 KB Views: 3

#### 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

• 25.2 KB Views: 4

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