Costs based on Start Date and End Date

[Tony2021]

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

[#2 · Accepted solution]

@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))

[#3]

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

[#4]

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.

[#5]

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

[#6]

Supposedly, the structured reference to B9 should act as a relative reference when you drag it to the right. But, I see it doesn't do that when the formula is part of the table (it does act as a relative reference when the formula is outside the table).

I'm afraid I don't have an answer other than what you've already discovered. Either use a non-structured cell reference or drag the formula to the right and then manually edit the formulas to reference the correct header.