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