where “code” is the named range K5:K9, and “value” is the named range L5:L9. Note: this is an array formula, and must be entered with control + shift + enter. 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: After MATCH runs, we have an array with 5 items: 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: This effectively forces INDEX to provide more than one value to the SUM function. After INDEX runs, we have: And the SUM function returns the sum of items in the array, 10. For a good write up on this behavior, see this interesting article on the EXCELXOR website.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
