Sum matched integer values of strings in a block of cells

Question

I don't know if this is possible to do without scripting, but I would like to have a block of cells that can be modified by the user where they will enter a string in each cell. For each of these string values I would like to retrieve a number from a table that matches that string and SUM together all of the resulting matched numbers.

With the matching table:

The users enters:

I would like to have the resulting SUM = 1 + 4 + 3 = 8

Answer 1

If your matching table is named namedRange1 and your chosen colours are in A9:A11:

=vlookup(A9,NamedRange1,2,0)+vlookup(A10,NamedRange1,2,0)+vlookup(A11,NamedRange1,2,0)  

Alternatively, name the values in the matching table by their corresponding colour and:

=Red+Yellow+Blue

Answer 2

I figured it out.

=SUM(ARRAYFORMULA(IF(UserInput <> "", VLOOKUP(UserInput, ColorMatcher, 2, false), 0)))

• SUM - Adds all of the values together
• ARRAYFORMULA - Allows the use of a "single target" function to work over an array of values, returns each individual lookup value to SUM
• IF(UserInput <> "") - Each of these values is compared to empty string first so that I can have blank cells in the range, otherwise VLOOKUP breaks due to a non-match, returns 0 otherwise
• VLOOKUP - Each value in UserInput is compared to a value in the first column of ColorMatcher, and takes the matching value in the 2nd column. In this case the index is not sorted