Forum Discussion
Costs based on Start Date and End Date
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))
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).
- 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.
- 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).
