How to create a dynamic VLOOKUP

The VLOOKUP function is one of the more popular Excel functions. It can readily interpret and report values in large data arrays. Often it finds it difficult to reflect changes to source data. Conversely a PivotTable must be manually updated, in order to reflect source data changes. A dynamic VLOOKUP that incorporates the COLUMN function will overcome this challenge of changing source data.


Practical Business Planning problems with a standard VLOOKUP

The VLOOKUP references a data array, which assumes static column numbering. Any column inserts to that data array, such as a table or dashboard summarising financial metrics, will render a standard, computed value as erroneous. This is demonstrated in the below video.

The following Excel spreadsheet is illustrated in the following video. It will help guide financial modellers to overcome potential erroneous cell referencing caused by a VLOOKUP.

vlookupStrategize Financial Modelling – Dynamic VLOOKUP example


How a dynamic VLOOKUP can overcome Col_index_num problems

Incorporate the Excel COLUMN function into the Col_index_num parameter of the VLOOKUP function. The following screenshot illustrates how a dynamic application of VLOOKUP will its accuracy. It will safeguard a financial model from erroneously referencing a data array, such as a dashboard of financial metrics.


In most companies, the VLOOKUP is better understood and used by professionals; as opposed to other, more complex Excel functions such as MATCH, SUMIF, SUMIFS or SUMPRODUCT. This is the very essence of best practice financial modelling – the importance of maintaining simplicity. Although it depends on the audience of the financial model, adhering to commonly used Excel functions (i.e. VLOOKUP) will suffice. 


Final point to remember

Be careful when deleting a column from the data array, because it could cause a referencing error and an error in the referenced value. Remember to apply absolute cell referencing to the table_array, if the formula will be copied over to adjacent cells. Relative cell referencing of a table array will result in a changing table array, when users copy the VLOOKUP formula over to adjacent cells.