Formula that multiplies corresponding data on one sheet to another

Ok so this is the problem I’m searching a solution for.

I have one excel file where I’m collecting substantial amount of data that is updated regularly regarding sales of different products. The data is automatically updated, and it is very important that there isn’t too much manual fixing in the future, it’s supposed to pretty much run itself.

Besides the data collection file, there is another file with percentages (placed on the second sheet for easy access now). I need the “percentages” tab to multiply with the corresponding data, I.e. D9 * C7. So the data in January 2015 for product A should multiply with the data on the percentages sheet for the corresponding month…

Do you have the formula I’m looking for that will find I.e. Product A and multiply the data with the matching month? If this is impossible I’m open to other innovative solutions, with the note that it must be able to update once new data comes in every month. I’m still very new to Excel so it’s possible there is a very easy formula that will solve this problem.
Very grateful for any help regarding this question… I made an attempt with vlookup but as you see it was not very good.

Hi Here2learn,

I hope the attached is suitable for what you required.

Thanks

Owen

Thanks a lot Owenaldrige!

It was very close to perfect, some of the percentages differ from month to month, is it possible to also match the months in that sheet as well? Sorry if it wasn't clear in the beginning.

Sorry,

I don't know how I missed that!

Please see revised workbook

Thanks

Owen

Thank you very much Owenaldrige. Do you know if it's possible to include the year with the month? The data I have goes a couple years back, I tried to change the formula you gave me to also include 2015 (the row above) but it doesn't seem to work.

Is it not possible or am I simply doing something wrong? I'd rather have the year and month as it is structured now, as this is the way the data is connected originally.

Hi Again,

Give this a try:

On Sheet 3 In Cell D9:
=IFERROR(VLOOKUP($A9,'Sales Data'!$A$6:$H$27,MATCH(D$7&D$6,'Sales Data'!$D$7:$H$7&'Sales Data'!$D$6:$H$6,0)+3,FALSE)*VLOOKUP($C9,Percentages!$B$5:$G$10,MATCH(D$7&D$6,Percentages!$C$6:$G$6&Percentages!$C$5:$G$5,0)+1,FALSE),"")

This is an array formula so Hold CTRL + SHIFT whiles pressing ENTER for it to work.

Then just drag this down and across.

Let me know if you have any problems!

Owen

Wonderful you have done it again!

Thank you.