Hello
anybody can help me solve a formula to check if the inv date fall between a promo allowance and if so get that promo allowance % and multiply by case, below is the data incentive table that im referring, I will like to bring the percentage
[TABLE="width: 396"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Family Product[/TD]
[TD]15% rebate[/TD]
[TD]Cat Incentive[/TD]
[TD]beginnig date[/TD]
[TD]end date[/TD]
[/TR]
[TR]
[TD]Oral Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Hair Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Fem Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Shave Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]15.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Fabric Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Family Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Home Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]2.5%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Baby Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]PCC[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Deodorants[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Oral Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Hair Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Fem Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Shave Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Fabric Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Family Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Home Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Baby Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]PCC[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Deodorants[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Skin Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Oral Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Hair Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Fem Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Shave Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Fabric Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Family Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Home Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]2.5%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Baby Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]2.5%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]PCC[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Deodorants[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Skin Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
</tbody>[/TABLE]
thanks
anybody can help me solve a formula to check if the inv date fall between a promo allowance and if so get that promo allowance % and multiply by case, below is the data incentive table that im referring, I will like to bring the percentage
[TABLE="width: 396"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Family Product[/TD]
[TD]15% rebate[/TD]
[TD]Cat Incentive[/TD]
[TD]beginnig date[/TD]
[TD]end date[/TD]
[/TR]
[TR]
[TD]Oral Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Hair Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Fem Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Shave Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]15.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Fabric Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Family Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Home Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]2.5%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Baby Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]PCC[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Deodorants[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]4/30/2018[/TD]
[/TR]
[TR]
[TD]Oral Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Hair Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Fem Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Shave Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Fabric Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Family Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Home Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Baby Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]PCC[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Deodorants[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Skin Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[/TR]
[TR]
[TD]Oral Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Hair Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Fem Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Shave Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]10.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Fabric Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Family Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Home Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]2.5%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Baby Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]2.5%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]PCC[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Deodorants[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
[TR]
[TD]Skin Care[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[/TR]
</tbody>[/TABLE]
thanks