Explanation
The heart of this formula is a basic INDEX and MATCH formula, used to translate text values into numbers as defined in a lookup table. For example, to translate "EX" to the corresponding number, we would use:
=INDEX(value,MATCH("EX",code,0))
which would return 4.
The twist in this problem however is that we want to translate and sum a range of text values in columns C through G to numbers. This means we need to provide more than one lookup value, and we need INDEX to return more than one result. The standard approach is a formula like this:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
After MATCH runs, we have an array with 5 items:
=SUM(INDEX(value,{2,2,3,2,5}))
So it seems INDEX should return 5 results to SUM. However, if you try this, the INDEX function will return only one result SUM. To get INDEX to return multiple results, we need to use a rather obscure trick, and wrap MATCH in N and IF like this:
N(IF(1,MATCH(C5:G5,code,0)))
This effectively forces INDEX to provide more than one value to the SUM function. After INDEX runs, we have:
=SUM({3,3,2,3,-1})
And the SUM function returns the sum of items in the array, 10. For a good write up on this behavior, see this article by Jeff Weir on the StackOverflow site.
Related formulas
Map inputs to arbitrary values
Map text to numbers
Return array with INDEX function
Translate letters to numbers
Related functions
INDEX Function
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....
MATCH Function
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined with the...
N Function
The Excel N function returns a number when given a value. The N function can be used to convert TRUE and FALSE to 1 and 0 respectively. When given a text value, the N function returns zero.
