Ok, so I figured out the formula/process but I can't get it to work correctly. the logic statement I need is:
=IF(AND('Sales Data by Channel'!$R$1:$V$1>=Assumptions!$C19,'Sales Data by Channel'!$R$1:$V$1<Assumptions!$D19),Assumptions!$C$6,IF(AND('Sales Data by Channel'!$R$1:$V$1>=Assumptions!$D19,'Sales Data by Channel'!$R$1:$V$1<Assumptions!$E19),Assumptions!$C$7,IF(AND('Sales Data by Channel'!$R$1:$V$1>=Assumptions!$E19,'Sales Data by Channel'!$R$1:$V$1<Assumptions!$F19),Assumptions!$B$9,IF('Sales Data by Channel'!$R$1:$V$1>=Assumptions!$F19,Assumptions!$C$9,0))))
This formula is to be replicated across five other columns under the April 2010 header. The formula produces the correct result ($3000) in the first column but produces a value of zero in the other 4. I have checked the formulas over and over and they are EXACTLY the same across all four rows but it will not come up with the correct value.
Any advice?