SOLVED

Costs based on Start Date and End Date

%3CLINGO-SUB%20id%3D%22lingo-sub-2451702%22%20slang%3D%22en-US%22%3ECosts%20based%20on%20Start%20Date%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2451702%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20file%20with%202%20sheets.%26nbsp%3B%20One%20sheet%20has%20amounts%20and%20the%20other%20has%20rates.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20rates%20sheet%20has%20a%20start%20date%20and%20end%20date%20and%20I%20need%20to%20use%20the%20rate%20that%20corresponds%20to%20the%20date%20of%20the%20amount%20on%20the%20amounts%20sheet.%26nbsp%3B%20Little%20difficult%20to%20explain%20but%20I%20think%20after%20you%20see%20the%20file%20it%20will%20make%20more%20sense.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20provided%20sample%20data%20of%20what%20the%20calculations%20should%20look%20like.%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20not%20clear.%26nbsp%3B%20Grateful%20for%20the%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2451702%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2451961%22%20slang%3D%22en-US%22%3ERe%3A%20Costs%20based%20on%20Start%20Date%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2451961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20use%20xlookup%20if%20you%20have%20that%20function.%20I%20believe%20it%20would%20be%3A%3C%2FP%3E%3CP%3E%3DB3*(30%2F360)*XLOOKUP(1%2C(tblPricing%5BInstitution%2FSource%5D%3D%24A10)*(tblPricing%5BStart%20Date%5D%26lt%3B%3D--B%249)*(tblPricing%5BEnd%20Date%5D%26gt%3B%3D--B%249)%2CtblPricing%5BRate%5D%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20I%20believe%20this%20will%20also%20work%20(will%20require%20you%20to%20hit%20Ctrl%2BShift%2BEnter%20after%20keying%2Fcopying%20into%20the%20formula%20bar%20instead%20of%20just%20enter)%3A%3C%2FP%3E%3CP%3E%3DB3*(30%2F360)*INDEX(tblPricing%5BRate%5D%2CMATCH(1%2C(tblPricing%5BInstitution%2FSource%5D%3D%24A10)*(tblPricing%5BStart%20Date%5D%26lt%3B%3D--B%249)*(tblPricing%5BEnd%20Date%5D%26gt%3B%3D--B%249)%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2457787%22%20slang%3D%22en-US%22%3ERe%3A%20Costs%20based%20on%20Start%20Date%20and%20End%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2457787%22%20slang%3D%22en-US%22%3EHi%20JMB%2C%20thank%20you%20for%20the%20crafty%20response.%20It%20works!%20I%20do%20have%20XLOOKUP.%20I%20assume%20that%20essentially%20everyone%20in%20Office%20365%20has%20it%20now%20but%20I%20am%20not%20sure.%3CBR%20%2F%3E%3CBR%20%2F%3EOne%20thing%20I%20noticed%20is%20that%20if%20you%20want%20to%20drag%20the%20formula%20you%20must%20use%20Control%20R%20or%20else%20the%20formula%20does%20not%20drag%20correctly.%20I%20think%20it%20is%20because%20of%20the%20table%20on%20the%20other%20sheet%20and%20how%20the%20table%20references%20work%20and%20can%20be%20quite%20annoying.%3CBR%20%2F%3E(%3CA%20href%3D%22https%3A%2F%2Fwww.accountingweb.co.uk%2Ftech%2Fexcel%2Ffive-flaws-in-excel-tables-and-how-to-fix-them%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.accountingweb.co.uk%2Ftech%2Fexcel%2Ffive-flaws-in-excel-tables-and-how-to-fix-them%3C%2FA%3E).%3C%2FLINGO-BODY%3E
Contributor

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

 

 

5 Replies
best response confirmed by Tony2021 (Contributor)
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))

 

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