Forum Discussion
Calculate a Fee if between a Date and on ProjID
=INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360)Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
=INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360)Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
- Hi, I am not sure what is happening but it says thre is a best response give to Grahmfs13. I do not see that response though?
Nice. Very nice.
Could I possibly add a tweak to it?
If the date is outside of the rates table, meaning that the date in the raw data is >[Date To] then fall back to the rate that is the according to the next closest date? Right now, it will return a #N/A if the date is > the [Date To].thank you very much.
=IFERROR(INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360),INDEX(Table2[Rate],MAX(IF(Table2[ProjID]=B2,ROW(Table2[ProjID]))-14))*[@Amt]*(30/360))Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Wow. I can sort of understand it.
I do have a question though when you have a sec.
The example posted was a simple example.
I have a different file in that the rates table is on another sheet.
I think my issue is the -14 part.How would I adjust knowing that the rate table is on another sheet?
Does -14 correspond to the header row for the rate table? ,
Do I need to reference the sheet that the rate table is on.
Also, I have 1 additional column in the rate table to the left of the rate (I dont think its material though).
The pic below is my production file (its a little different from the attached xl).
You can see my rate table is on another sheet and my data is on the sheet named "data".
==>I assume I would put -3 where the -14 is?
I tried this but it didnt seem to work (still says #NA if outside the dates max but in your file posted it works perfectly)
=IFERROR(INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360),INDEX(Table2[Rate],MAX(IF(Table2[ProjID]=B9,ROW(Table2[ProjID]))-14))*[@Amt]*(30/360))
