title: MAX value using lookup for comma-separated list in one of the columns in Excel
body: <p>This formula is SO close to what I am looking for.</p>
  
  <pre><code>Keys            Scores          
  k1  10          k1,k2
  k2  15          k3
  k3  8           k1,k2,k3
  
  
  =SUMPRODUCT(SUMIFS(B:B,A:A,TRIM(MID(SUBSTITUTE(E2,",",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(E2)-LEN(SUBSTITUTE(E2,",",""))+1))-1)*999+1,999))))
  </code></pre>
  
  <p>It works great but instead of it providing a SUM of the Scores I like it to provide the max value of the Scores based on the comma separated string entered.</p>
  
  <p>Can it be done?</p>
  
  <p>Tried substituting MAX and MAXA for SUMPRODUCT</p>
  
  <p><a href="https://i.stack.imgur.com/P7IJJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/P7IJJ.png" alt="Example of current functionality."></a></p>
  
  <p>In this example my dep_val indicates the End Date that I want to reference to populate the Start Date while allowing only workdays and no holidays and it works well.</p>
  
  <p>What I would like to do is enter a string such as 3,4,5 as the dep_val for Task 6 and have the formula return the MAX value End Date in Tasks 3,4,&amp; 5.</p>
  
  <p>Hope this clears things up.</p>