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.
Bookmarks