Thanks! The formula returned the correct answer in all cells except C3. Could it be that the portion of the formula where G2:G5 <=B3 is deemed “true” for both dates 12/1/15 and 3/1/16 so the answer defaults to the first “true” it encounters or does the answer default to the MIN price when there are two “true” possibilities? Thanks again!
Unknown
| A | B | C | D | E | F | G | H |
---|
Product Code | Date Sold | Selling Price | Product Code | Price Effective Date | Price | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]1/15/2016[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]12/1/2015[/TD]
[TD="align: right"] $ 125.00 [/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]3/1/2016[/TD]
[TD="bgcolor: #FF99FF, align: right"]125[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]3/1/2016[/TD]
[TD="align: right"] $ 130.00 [/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]2/25/2016[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]1/1/2016[/TD]
[TD="align: right"] $ 215.00 [/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]4/8/2016[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]4/15/2016[/TD]
[TD="align: right"] $ 245.00 [/TD]
</tbody>
Sheet1
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=MIN(
IF($F$2:$F$5=A2,IF($G$2:$G$5<=B2,$H$2:$H$5)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]{=MIN(
IF($F$2:$F$5=A3,IF($G$2:$G$5<=B3,$H$2:$H$5)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]{=MIN(
IF($F$2:$F$5=A4,IF($G$2:$G$5<=B4,$H$2:$H$5)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=MIN(
IF($F$2:$F$5=A5,IF($G$2:$G$5<=B5,$H$2:$H$5)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]