I have an excel file that has prices per productId combination ( columns A and B ) and by effective dates.
As you can see it may contain duplicates for the productId combination and they are valid only after the date has passed.
This is the formula i came up with till now but it only works if the rows are sorted by A B and C ( especially C should be sorted descending).
In the example i am expecting the correct result price to be 600.00 but it returns 200.00 .
I understand why this happens. But i don’t know how to fix it and make it work correctly.
Correctly means :
Give the product ids:
if effectiveDate >= 07/18/2019 00:00:00 => 600.00
if effectiveDate between 07/07/2019 00:00:00 AND 07/18/2019 => 400.00
if effectiveDate between 06/01/2019 00:00:00 AND 07/07/2019 => 200.00
if effectiveDate before 06/01/2019 00:00:00 => N/A
Any help to make this formula work will be appreciated