Summing up for specific criteria

[PapaCheese]

I'm hoping someone can help me out here.

I have 2 tables. Each table has 2 columns. In the left hand column is a letter or a letter followed by a number, and in the right hand column is a numeric value that corresponds with the left hand column.

I want to be able to enter multiple letters and/or letters with numbers into a cell, for example A,B,A1,C,D,A3, and have the corresponding values of those letters summed up in the adjacent cell.

I'd imagine it would be a sumproduct but I can't work out how use it for specific criteria.

How would I do this?