Excel: Dynamic Column Reference in a Financial Model

  • Peter Lynch

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