Calculate a Fee if between a Date and on ProjID

[Tony2021]

Hello Experts,

I need to apply a rate to the excel table based on the following criteria:

1. if the [date] is between the rate table [Date From] and [Date To]

2. also on [ProjID]

The tricky part to me adding the [ProjID] criteria. 

grateful for the help.  

Let me know if there are any questions. 

I have attached a sample file. 

[#2 · Accepted solution]

@Tony2021 

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

[#3]

@OliverScheurich 

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.

[#4]

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?

[#5]

@Tony2021 

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

[#6]

@OliverScheurich

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

[#7]

@Tony2021 

=IFERROR(INDEX(Table3[Rate],MATCH(1,([@ProjID]=Table3[ProjectID])*([@Date]>=Table3[Date from])*([@Date]<=Table3[Date to]),0))*[@Amt]*(30/360),INDEX(Table3[Rate],MAX(IF(Table3[ProjectID]=B2,ROW(Table3[ProjectID]))-3))*[@Amt]*(30/360))

Maybe with this formula. I added some rows in your rate table on the sheet "Rates" in order to check if the formula works as intended.

The name of the sheet "Rates" doesn't appear in above formula. Only the name of the table which is Table3 is referenced in the formula.

The -14 corresponds to the header row of the rate table. In other words: The data of the rate table is in rows 15 to 19 of the worksheet. These rows are rows 1 to 5 within the rate table (Table2). Consequently i had to enter -3 in the above formula for Table3.

[#8]

Perfect. thats an amazing set of skills you have. thanks again for the help!