VLookup or Sumproduct or combined? This is getting out of hand

Hi!

This is my first post, but i have regularly used this forum to figure out my issue, however this one i cannot get and I am hoping for some help.

I have a sheet that has items on it, that contain ingredients. The ingredients can be in multiple instances.

On the other sheet i have a schedule of what is to be produced and the qty and this counts the qty required from the first sheet.

There are 6 different productions so i have listed all indvidually and then done a SUMPRODUCT for each one and added it all together, however this makes it unwieldy. Currently i use =SUMPRODUCT((Master!$A$2:$A$2210=Schedule!$E$1)*(Master!$H$2:$H$2210=Schedule!$A14)*(Master!$L$2:$L$2210)*E$2) and then add the same formula 6 times (for each item being produced) to come up with a total, surely there is a better way?

I can provide a sample of some data if that helps to understand what I am on about?

Thanks Grant

Thanks for the response, i have made an example spreadsheet which i will attach, hopefully it should be pretty straight forward, i would like to change the SUMPRODUCT formulas on sheet 1 (schedule) to something more concise as there are going to be 1000's of items looking up this formula.

Ahh yes that is right, the formula in F14 should be attributed to the F column. I must of accidentally changed the formula when i made the example, in my full spreadsheet the formula works however i just want to make it more concise as there will be over 1000 items populating the full spreadsheet.

Yes! Thats much better thanks! Why didnt i think of changing those ranges to sequences instead of alternating them.. Thanks again!