Excel sum column with values derived from a lookup table

Hi,

I'm looking for a function that will sum a set of values derived from entries in a column, but stored in a lookup table.

For example:

	Column 1	Column 2	Column 3
Row 1	B	A	D
Row 2	B	B	A
Row 3	B	C	B

Where say in this case A=10, B=40, C=100, D=250. So you would expect the formula to return Column 1 = 120, Column 2 = 150, Column 3 = 300

However, these values may change, so I'm looking for a dynamic formula that includes a lookup to a table where this mapping is stored rather than a series of nested IF statements or SUMIFS.

Understand I could also write a simple macro to do this, but would prefer not to if there is another solution.

I have tried various things including an array formula using SUM/INDEX/MATCH, but have had no luck so reaching out!

Any help would be appreciated thanks.

Hi and welcome to the forum

My 1st reaction is to suggest some helper columns (which you can hide), where you lookup the value in the table, then sum the helper.

Some-one will probably offer a better option, but give this 1 some thought too

How do you arrive at those results?

Column A should = 330
Column B should = 60
Column C should = 300

The formula that will give you those results...

Create the lookup table:

F1: A...G1: 10
F2: B...G2: 40
F3: C...G3: 100
F4: D...G4: 250

Then, this formula entered in A5 and copied across:

=SUMPRODUCT(SUMIF($F1:$F4,A1:A3,$G1:$G4))

You're welcome. Thanks for the feedback!