Evaluate a list (coma separated) based on numeric values form a vlookup

Question

two data set, couldn't be more gnarled

first, among plenty of columns there are one with keywords coma separated, aka:

Id ---- PLAYER -- KEYWORDS
 1 ---- Jonh ---- futball, waterpolo , tennis
 2 ---- Anna ---- soccer, swiming, running
 3 ---- Marc ---- runnign, waterpolo 
 4 ---- Peter --- running

and a "ponderation" table with numeric values for all keywords

SPORT-- USE_BALL -- IN_WATER
futball --- 1 --------- 0
waterpolo - 1 --------- 1
tennis ---- 1 --------- 0
swiming --- 0 --------- 1
running --- 0 --------- 0

and as a result, I would like yo have a table, with a "SUM" of this caracteristics, aka:

Id ---- PLAYER -- PLAY_WITH_BALL --- PLAY_IN_WATER ---- KEYWORDS
 1 ---- Jonh ----------- 3 ---------------- 1 --------- futball, waterpolo , tennis
 2 ---- Anna ----------- 1 ---------------- 1 --------- soccer, swiming, running
 3 ---- Marc ----------- 1 ---------------- 1 --------- runnign, waterpolo 
 4 ---- Peter ---------- 0 ---------------- 0 --------- running

what would be the aproach ? preferable without macros

Answer 1

Another shorter formula option,

In K2, formula copied right to L2 and all copied down :

=SUMPRODUCT(ISNUMBER(SEARCH($E$2:$E$6,$M2))*F$2:F$6)