Excel totals via a value lookup

Question

How can the following be calculated using Excel?

I have a table on the left containing certain letters, and a lookup table on the right to convert the letters into values. I am trying to calculate the totals in column G and row 5.

My current solution involves duplicating the values in hidden rows underneath using the following formula for each cell:

=VLOOKUP(B3,$I$3:$J$5,2,FALSE)

This would give me values:

1   1   1   1   1
1  55   1   4   1

From that a simple =SUM() gives the required totals.

Is there a way to create the totals without needing to first build a set of VLOOKUP() values?

Answer 1

I was going to suggest this in G3

=SUMPRODUCT(N(OFFSET($J$2,MATCH(B3:F3,$I$3:$I$5,0),0)))

and this in B5

=SUMPRODUCT(N(OFFSET($J$2,MATCH(B3:B4,$I$3:$I$5,0),0)))

as an alternative (because you can't use VLOOKUP with an array of lookup values but you can use MATCH that way).

You could do the whole table like this if you wanted to

=SUMPRODUCT(N(OFFSET($J$2,MATCH(B3:F4,$I$3:$I$5,0),0)))

Answer 2

Try:
G3:

=SUM(IF(B3:F3=I3:I5,J3:J5))

Enter with 🅲🆃🆁🅻+🆂🅷🅸🅵🆃+🅴🅽🆃🅴🆁