title: Find a MAX and SUM a variable number of columns in Excel without Macros
body: <p>I have a table in Excel:</p>
  
  <pre><code>    A    B    C    D    
          Day1 Day2 Day3
  1 Ron    3    2    2  
  2 Don    4    2    1
  3 Ton    1    5    2
  </code></pre>
  
  <p>On a different worksheet, I need to produce a table of the type (without using macros):</p>
  
  <pre><code>    A    B    C    D    
          Ron  Don  Ton
  1 Ron    -    -    -  
  2 Don    8    -    -
  3 Ton   10   11    -
  </code></pre>
  
  <p>Where the value is the MAX of each pairs value for each days, summed across all days. So if it was just for 3 names and days, I would just use the formula below with a VLOOKUP to see what the value for each name is on a particular day and copy/paste it for each day. (Side note, actually my project is not that big, so by the time I posted this question, I could have been done with it, but I really want to learn how to do this in a more intelligent way).</p>
  
  <pre><code>=SUM(MAX( VLOOKUP(Table2!$A2,Table1!$A:$D,2,FALSE), VLOOKUP(Table2!B$1,Table1!$A:$D,2,FALSE)), and so on Day2, Day3...
  </code></pre>
  
  <p>I tried the following:</p>
  
  <pre><code> {=SUM(MAX(VLOOKUP(Table2!$A2,Table1!$A:$D,{2,3,4},FALSE),VLOOKUP(Table2!D$1,Table1!$A:$D,{2,3,4},FALSE)))}
  </code></pre>
  
  <p>However, apparently the VLOOKUP can't return an array (and INDEX MATCH can't either).</p>
  
  <p>Any help would be much appreciated.</p>