The VLOOKUP function is a highly useful Excel function in financial analysis and business planning. It is a valuable one-dimension lookup tool – along with HLOOKUP. However in some cases, a financial analyst will require a two-dimension lookup tool, in order to reference the exact value from a data table, which a vanilla VLOOKUP function will be unable to perform.
The combination of the INDEX function with two MATCH functions can solve this matter. The following Excel spreadsheet will be used to illustrate the value of INDEX MATCH:
Strategize Financial Modelling – INDEX MATCH example
[youtube]http://www.youtube.com/watch?v=9OUd7r_AgGk[/youtube]
INDEX MATCH
Adopting the INDEX function to reference the table array, and then looking up the y-axis of data and then the x-axis of data; users will be able to dynamically and correctly lookup the exact value from a two-dimension table array.
Practical business planning or financial modelling uses
The application of the INDEX MATCH for financial modelling can be extensive:
- Foreign exchange translation of financial statements
- Application of appropriate interest rate for a certain tranche of debt based on duration and type
- Executive dashboard presentation to company executives
Step by step approach
INDEX function
INDEX references the table of data. It must be all encompassing, otherwise the overall formula will not work and additions to the table will make the formula erroneous.
It is paramount to use absolute formula range referencing or create a defined name for the formula range; otherwise the reference range will change, if the formula is copied to another cell.
❶ Array
The data array must encapsulate the outliers of the y-axis and x-axis of the table. It is wise to use absolute cell referencing as in this example in cell H22 for the first example shown in the above video:
=INDEX($B$6:$R$18,
❷ MATCH function (y-axis)
The first MATCH relates to the values running vertical or top to bottom in the array. Like the previous Array referencing, it is advisable to apply absolute cell referencing to the rows of values (i.e. countries). MATCH type will always be “0” – or an exact match for the overall formula to work.
MATCH($F22,$B$6:$B$18,0),
❸ MATCH function (x-axis)
This MATCH refers to the data running from left to right at the top of the data array. Similar to the MATCH function for the y-axis, it is fundamental to reference the correct horizontal outlier, as per the array in the INDEX function – else the formula won’t function correctly.
MATCH(G$22,$B$6:$R$6,0))
Important practical considerations with INDEX MATCH
Be mindful of the impact on the formula if new y-xis (countries) or x-axis (financial or operational metrics) are added in the future. If this is not taken into consideration, then the INDEX reference to the data array and the MATCH functions could deliver an erroneous result.
The alternate approach through the application of defined names for the table array (“tblArray”), y-axis (“yAxis”) and x-axis (“xAxis”) illustrated in the final example, is one way to ensure the correct referencing occurs (as in cell J22).
=INDEX(tblArray,MATCH($F22,yAxis,0),MATCH(G$22,xAxis,0))