MAX value using lookup for comma-separated list in one of the columns in Excel

Question

This formula is SO close to what I am looking for.

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))))

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.

Can it be done?

Tried substituting MAX and MAXA for SUMPRODUCT

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.

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,& 5.

Hope this clears things up.

Answer 1

Formula solution for Excel 2013 or above in the use of FILTERXML function

Formula in F2, copied down :

=AGGREGATE(14,,LOOKUP(FILTERXML("<a><b>"&SUBSTITUTE(E2,",","</b><b>")&"</b></a>","//b"),A$2:B$4),1)

Edit: The above formula Lookup list must be sorted in ascending order because the using of LOOKUP function and it is a normal formula.

Or,

You can use the VLOOKUP function without sorting instead, and it is an array formula must confirmed with SHIFT+CTRL+ENTER entry

=MAX(VLOOKUP(T(IF({1},FILTERXML("<a><b>"&SUBSTITUTE(E2,",","</b><b>")&"</b></a>","//b"))),A$2:B$4,2,0))

Answer 2

You can use MAX() instead of SUMPRODUCT() but then you need to enter the formula as an array formula with Ctrl+Shift+Enter.