• 07/01/2018
    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 videoLINK