Excel pickup wrong data

The issue is probably because you use only month and not year information. You could add checking yera by:

(depending on your separator regional settings you probably need to change all semicolons to commas)

Formula:

=IF(Y$1="Actual";SUMPRODUCT(Historical!$E$3:$BM$12*(Historical!$E$1:$BM$1=MONTH(Y$2))*(YEAR(Historical!$E$2:$BM$2)=YEAR(Y$2))*(Historical!$A$3:$A$12=MRP!$A3));SUMPRODUCT(Forecast!$E$3:$Y$1001*(Forecast!$B$3:$B$1001=MRP!$B3)*(Forecast!$E$2:$Y$2=MRP!Y$2)))

Another idea - instead of comparying month chcech whether date is before end of current month (you have it in row 2) and end of previous month, so:

Formula:

=IF(Y$1="Actual";SUMPRODUCT(Historical!$E$3:$BM$12*(Historical!$E$2:$BM$2<=Y$2)*(Historical!$E$2:$BM$2>EOMONTH(Y$2;-1))*(Historical!$A$3:$A$12=MRP!$A3));SUMPRODUCT(Forecast!$E$3:$Y$1001*(Forecast!$B$3:$B$1001=MRP!$B3)*(Forecast!$E$2:$Y$2=MRP!Y$2)))

This formula was used in attached file, so when you open it, it will use the same separator as you are normally using.

Glad to hear it works.

It would be now a good idea to mark thread as Solved. Above your first post you will find Thread Tools to do it.

It would be lovely if you could also "add me some reputation" by clicking a small star icon in lowerleft corner of my post and then confirming in a pop-up message (note that some web-browsers try block this pop-up).

Thanks for the reputation point !