Excel: Dynamic Column Reference in a Financial Model
This video explains how to use SUMIF and the INDEX + MATCH function combination in Excel to dynamically reference columns in a financial model. This has been particularly useful to me when I am organizing a company's data by location or product category.
Excel Trick: This formula relies on one input in the INDEX function. If you look to the formula below you will notice that the "row_num" input in the INDEX function is the value zero (highlighted red for emphasis). With this input the INDEX function will return the entire column.
=SUMIF( ... ,INDEX($B$13:$G$13,0,MATCH($C9,$B$13:$G$13,0)))
Note: Ellipsis used above to emphasis INDEX + MATCH portion of the formula. Please see video for the inputs that replace the ellipsis.
If you are unfamiliar with the SUMIF function I would recommend watching this video first. A tutorial on the INDEX + MATCH function combination can be found in the Excel for Models video series (subscriber content).
Download template in video: LINK