Index-Match for Pricing Effective Dates

[mikesensei]

Hi Everyone,

I am working on a document trying to compile monthly purchases of parts for the year. The biggest obstacle I am facing is that the pricing for different parts changes at different points throughout the year. Currently I have one table listing each part with the effective and expiration dates of each price, similar to this:

[TABLE="width: 547"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD]Supplier[/TD]
[TD]Effective Date[/TD]
[TD]Expiration Date[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Bracket[/TD]
[TD]Pacific[/TD]
[TD="align: right"]4/1/2016 0:00[/TD]
[TD="align: right"]4/1/2017 0:00[/TD]
[TD] $ 0.3219[/TD]
[/TR]
[TR]
[TD]Bracket[/TD]
[TD]Pacific[/TD]
[TD="align: right"]4/1/2017 0:00[/TD]
[TD="align: right"]7/1/2017 0:00[/TD]
[TD] $ 0.3347[/TD]
[/TR]
[TR]
[TD]Bracket[/TD]
[TD]Pacific[/TD]
[TD="align: right"]7/1/2017 0:00[/TD]
[TD][/TD]
[TD] $ 0.3401[/TD]
[/TR]
[TR]
[TD]Spring[/TD]
[TD]BB Springs[/TD]
[TD="align: right"]4/1/2013 0:00[/TD]
[TD="align: right"]4/1/2017 0:00[/TD]
[TD] $ 0.2023[/TD]
[/TR]
[TR]
[TD]Spring[/TD]
[TD]BB Springs[/TD]
[TD="align: right"]4/1/2017 0:00[/TD]
[TD][/TD]
[TD] $ 0.2225[/TD]
[/TR]
</tbody>[/TABLE]

What I am hoping to do is reference the purchase price for each part for each month, so I can multiply by receipts and calculate the total dollar amount of purchases. My goal is to use INDEX-MATCH (or another function if there's a better option) to get it into a table like this:

[TABLE="width: 447"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 4"]Purchase Price[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[/TR]
[TR]
[TD]Bracket[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Spring[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Using Excel 2013 on a PC. Any ideas you have would be much appreciated!

 

[mart37]

Spreadsheet Formulas

Cell Formula B11 {=INDEX($E$2:$E$6,SUMPRODUCT(($A11=$A$2:$A$6)*(B$10>=$C$2:$C$6)*((B$10<$D$2:$D$6)+(ISBLANK($D$2:$D$6) )),ROW($1:$5)))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

 

[mart37]

=sumifs($e$2:$e$6,$a$2:$a$6,$a11,$c$2:$c$6,"<"&b$10,$d$2:$d$6,">="&b$10)+sumifs($e$2:$e$6,$a$2:$a$6,$a11,$c$2:$c$6,"<"&b$10,$d$2:$d$6,"")