Situation: Downloading sales from ERP Accounting System on a line by line invoice detail level (i.e. every item on the invoice gets pulled out) for all the invoices.
Attempting to calculate promotion accruals based on Item#, Customer Code, and Date Range.

I've been searching the internet for possible formulas / structures that would help me get the right information pulled. The closest I've gotten is this formula:

=INDEX('PCS Sheets'!$J:$J,MAX(IF(($C3>='PCS Sheets'!$E:$E)*($C3<='PCS Sheets'!$F:$F),MATCH($AP3,'PCS Sheets'!$A:$A,0))))

Invoice Detail Information:

Customer 1
Product A
Product B
Product C
Invoice Date: 2/20/2014

Promotion Information:

Customer 1
Dates: 1/15/2014 - 2/28/2014
Product A
Rate: 0.70
Dates: 1/15/2014 - 2/28/2014
Product C
Rate: 0.85

PRODUCT B has no promotions and should show ZERO for Scan/Unit rate.

Issues:
1. Calculations don't appear to work in every instance when I try to multiply the rate and the quantity in another column to get the accrual. As these formulas are entered as arrays, are there different calculation priorities given to arrays?
2. Invoices where there isn't a related scan, it's pulling the header row information that corresponds to those columns. (i.e. the cell populates with Scan/Unit)... Currently attempting to fix that with an If-Then, but calculation time is very slow.

Any ideas? I saw someone used a SUMIFS and SUMPRODUCT formula to pull the information on other examples, but I couldn't get that to work either.

Let me know if any other information is needed to assist on this. Open to ideas and suggestions on getting the information pulled so I can create accrual calculations.

Please any help would be appreciated.

Thanks.

JMData Consultant