title: How can I multiply lookup values from two different tables and then sum them in just one cell?
body: <p>I am trying to get a dedicated material table in excel. So we have a few products and these products require particular materials. I know how much and which materials go in particular products. I also know how much is sold in which year, now I want to calculate the required materials for these years. Because the productbase is large (>100), and thus >100 columns, I would like to use some lookup or index function to automate the multiplication. </p>
  
  <p>As shown in the picture, I tried using a sumproduct, which was also explained in some other question on stackoverflow. This sumproduct should multiply all values obtained in one table with the corresponding values in the other. I feel that something is not right about my first two match functions (see picture again)</p>
  
  <p><a href="https://i.stack.imgur.com/kIs4M.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kIs4M.png" alt="enter image description here"></a></p>
  
  <pre><code>The code used:
  =SUMPRODUCT(INDEX($B$19:$E$22;MATCH(B$2;$A$19:$A$22;0);MATCH(B$10;$B$18:$E$18;0));INDEX($B$3:$E$5;MATCH($A11;$A$3:$A$5;0);MATCH(TRUE;$B$3:$E$5&gt;0;0)))
  </code></pre>
  
  <p>The image contains some extra info and explanation of the actual need</p>
  
  <p>The reason that it needs a lookup or index is because the products in table 3 are always in another order than what is shown in table 1.</p>
  
  <p>I would like to have this sumproduct as automated as possible, thank you in advance:)</p>