#StackBounty: #excel #indexing #match #formula Excel Match Index multiple criteria and multiple dates

Bounty: 50

enter image description here

=INDEX(D1:D6;MATCH(1;(E1=A1:A6)*(E2=B2:B6)*(DATEVALUE(MID(E3;1;10))>=DATEVALUE(MID(C1:C6;1;10)));0))

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:
D318JV 00B6

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


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.