Adding multiple cells that need lookups

[TMervis]

Table 1 has various columns. Each column has a alpha_numeric code. Table 2 has the corresponding numeric value for each code. I want to total each row in Table 1 using a formula (Vlookup, Index ?). Also, the formula should be able to accommodate additional columns that will need to inserted in the future, for example, between COL_C and COL_D. Thanks

TABLE_1
	COL_A	COL_B	COL_C	COL_D	TOTAL
Codes	aa1	bb2	cc3	dd4	?????
TABLE_2
Code	Value
aa1	234
bb2	456
cc3	321
dd4	56

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Scott T]

	A	B	C	D	E
1	aa1	bb2	cc3	dd4	1067
2
3	aa1	234
4	bb2	456
5	cc3	321
6	dd4	56

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

In E1 This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula. Adjust the ranges as need.

=SUM(SUMIF(A3:A6,A1:D1,B3:B6))

 

[Dave Patton]

try
=SUMPRODUCT(SUMIF(A3:A6,A1:D1,B3:B6))