Forum Discussion
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 date of the amount on the amounts sheet. Little difficult to explain but I think after you see the file it will make more sense.
I have provided sample data of what the calculations should look like.
Let me know if not clear. Grateful for the assistance.
thank you
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))
5 Replies
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))
- HI JMB,
Not sure if you are following this thread. I have an issue it seems when I reproduce on my side. It is because my table is formatted as a table. When I refer to the date as in cell B9 in your formula it puts the name of the table and the actual date and I can not drag it since the date does not change to the date in the adjacent cell.
Not sure if you are following me but I only have this issue if the table is formatted as a table. I thought hitting control then R after selecting the formula it would adjust but the date is fixed.
do you know how to fix this issue? I can reproduce this same issue in the file you uploaded after I format the table as a table and adjust the formula date cell then drag the formula out to the right.- I think I have the solution. Instead of clicking on the cell in the table, I need to manually type the cell reference that holds the date. This will remove the "structured reference" and revert back to cell reference to allow dragging (noting I use control R).
- 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).
