Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Jun 15, 2021
Solved

Costs based on Start Date and End Date

Hello,   I have attached a file with 2 sheets.  One sheet has amounts and the other has rates.  The rates sheet has a start date and end date and I need to use the rate that corresponds to the dat...
excel
  • JMB17's avatar
    JMB17
    Jun 15, 2021

    Tony2021 

     

    You could use xlookup if you have that function. I believe it would be:

    =B3*(30/360)*XLOOKUP(1,(tblPricing[Institution/Source]=$A10)*(tblPricing[Start Date]<=--B$9)*(tblPricing[End Date]>=--B$9),tblPricing[Rate],0)

     

    Alternatively, I believe this will also work (will require you to hit Ctrl+Shift+Enter after keying/copying into the formula bar instead of just enter):

    =B3*(30/360)*INDEX(tblPricing[Rate],MATCH(1,(tblPricing[Institution/Source]=$A10)*(tblPricing[Start Date]<=--B$9)*(tblPricing[End Date]>=--B$9),0))

     

JMB17's avatar
JMB17
Bronze Contributor
Jun 15, 2021

Tony2021 

 

You could use xlookup if you have that function. I believe it would be:

=B3*(30/360)*XLOOKUP(1,(tblPricing[Institution/Source]=$A10)*(tblPricing[Start Date]<=--B$9)*(tblPricing[End Date]>=--B$9),tblPricing[Rate],0)

 

Alternatively, I believe this will also work (will require you to hit Ctrl+Shift+Enter after keying/copying into the formula bar instead of just enter):

=B3*(30/360)*INDEX(tblPricing[Rate],MATCH(1,(tblPricing[Institution/Source]=$A10)*(tblPricing[Start Date]<=--B$9)*(tblPricing[End Date]>=--B$9),0))

 

Tony2021's avatar
Tony2021
Iron Contributor
Jun 17, 2021
Hi JMB, thank you for the crafty response. It works! I do have XLOOKUP. I assume that essentially everyone in Office 365 has it now but I am not sure.

One thing I noticed is that if you want to drag the formula you must use Control R or else the formula does not drag correctly. I think it is because of the table on the other sheet and how the table references work and can be quite annoying.
(https://www.accountingweb.co.uk/tech/excel/five-flaws-in-excel-tables-and-how-to-fix-them).

Resources