Solved: Sum values associated with keywords inside cell

I have a spreadsheet in which I need to do something unusual.

The setup:
Each cell will contain a list of keywords. Each keyword is worth a specific value (1-4). A cell may contain any number of keywords in any order, however each keyword only repeats once.

Data sheet

source	value
Bob	1
Mary	3
Hill	2
Brown	4

Results sheet

input	value list	value sum
Phil,Bob,Brown

Part1 :Getting a list that transposes the words in the cell to a list of associated values.

In the results sheet I expect the value list to return 1, 2, 4

I've done this 2 ways but I feel both are ugly and nonuser friendly, would be great if someone had a better/simpler way to do this.

These work but are ugly:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Part2 : Sum up the associated values for every word in the cell.

[S]I've looked at sumifs and sumproduct but I couldn't get this to work! [/S]

[Formula]
=SUMPRODUCT(--ISNUMBER(SEARCH(Data!A2:A5,A2)),Data!B2:B5)[formula]


Note: I have an example spreadsheet but the upload isn't working.