title: Sum the product of multiple vlookup or index/match results
body: <p>I have 2 worksheets, one with multiple columns of data where rows are given years and the other columns are values under the headers of names, and the second sheet lists rates by year (column) and by name (row).</p>
  
  <p>I am trying to take each value in a given column of sheet1 and multiply it by the relevant rate (found by matching the year of each row and the name of its column to the table in sheet2) and then sum the products by name, but can only use a single cell to do all this.</p>
  
  <p>So far I've tried <code>SUMPRODUCT</code> and got to <code>=SUMPRODUCT(O$24:O$514,INDEX(Rates!$A$2:$X$200,MATCH(O$23,Rates!$A$2:$A$67,0),MATCH($D$24:$D$514,Rates!$A$2:$X$2,0)))</code> but this seems to fall down at the <code>MATCH($D$24:$D:$514,Rates!$A$2:$X$2,0)</code> part, even entering as an array.</p>
  
  <p>I've currently resorted to a clumsy series of <code>SUMIF(2016)*INDEX/MATCH(2016 rate)+SUMIF(2015)*INDEX/MATCH(2015 rate)</code> etc, but would really like to have forward compatibility without relying on formulae being updated!</p>
  
  <p><a href="http://i.stack.imgur.com/Fhc01.png" rel="nofollow">slightly simplified image of the workbook, thanks for any help!</a></p>