Add multiple values from a table, with multiple criteria pulled from same cell

[Dalcassius]

I have two tables. The first is a reference for values.The second is a sum of associated values.
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]-3[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]-4[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]B, E[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B, C, E[/TD]
[/TR]
</tbody>[/TABLE]
I am trying to find a way to take the values in the second column of the second table, use them to search the first column of the first table, and use the associated values in the second column of the first table to create their sums in the first column of the second table.
I am beginning to think this is out of the scope of a formula. I am no good with VBA script which I why I haven't tried that path.
I started by looking for a way to get VLOOKUP to use multiple criteria, but I can't find a method that uses a single cell with multiple criteria. I then moved on to INDEX/MATCH with CHOOSE, but that was a dead end for me.

I would be happy to even find out this is impossible so I can put it to bed.

 

[Special-K99]

I just saw something about an hour or two ago that would solve this. Let me have a look round...

 

[Special-K99]

Here's what I was looking at

http://www.mrexcel.com/forum/excel-questions/878398-want-understand-below-formulae.html#post4257111

Adds up the digits in a number using SUMPRODUCT ( MID* ROWS( INDIRECT(...)))
Could you base something on this with a VLOOKUP

I've got

=SUMPRODUCT(--(VLOOKUP(MID(E1,ROW(INDIRECT("1:"&LEN(E1))),1),$A$1:$B$8,2,0)))

But it's only picking up the first letter

 

[Dalcassius]

Thanks for the reply Special-K99. I played with SUMPRODUCT but was unable to figure out a way to get it to work properly. A co-worker then pointed out it isn't a formula issue, it's a formatting issue.

I broke the second column of the second table into half a dozen in-cell drop downs. I then copied the entire second table into a second page. There I replaced the drop downs with VLOOKUP, which pulled the criteria from the drop downs on the first page, search the first table and output the numbers. The first column on the second page SUM'd the lines of VLOOKUP's, and the identical cells on the first page then copied the totals from the second page. I then hid the second page.

And now the spreadsheet looks pretty slick, if I do say so myself.