Although Vlookup is very helpful in financial modelling, there are other alternatives. Choose can sometimes provide the shortest neatest solution to getting data in the right place in a financial model, especially when data sets are small. Some of our modelling course delegates prefer “Index”, and you’ll see that function regularly employed in financial models.
Getting started with Excel’s Index function
Index is perhaps simpler and easier to understand than Vlookup. All you need to do is point Excel to a table of data. You also need to tell Excel what row and column you are interested in. Excel will then provide you with the data point at the intersection of the row and column.
Excel’s Index function: an example
Please download the Excel spreadsheet for an example of the Index function at work. Look closely at cell C19 – clicking on Excel’s “fx” button (LHS of the task bar or “Shift” “F3” is the keyboard shortcut) may help.
Index vs. Vlookup
Of course, within your financial model, you still need to find a way of telling Excel what row or column you are interested in. This might explain why Vlookup can be more useful than Index (built into Vlookup is the ability to work out which row you’re interested in).
Please download the Excel spreadsheet for a comparison of Index vs Vlookup.
Index with Match and Count
You’ll often see financial modelling practitioners combining Index with Excel’s “Match” or “Count” functions. These two functions can be helpful in determining which row and column should be used for the Index function. Please download the Excel spreadsheet for an example of where Match is being used as an input into Index. Look closely at cells B19 and also C19.
Here you can download an Excel spreadsheet where Excel’s Count function is being used to determine the row for an Index function. See cell B19.
Note the Count function counts the number of numbers, explaining why values have been entered in cells G19:G22.
Offset vs. Index
Excel’s Offset function can be used to solve the same kind of problem as Index. Index finds the intersection of a particular row and column in a table of data. Offset counts the rows and columns from a given starting point. See cell C19 in the Index vs. Offset spreadsheet download for an example of how Offset could be applied to solve this Excel financial modelling problem.
Why Index or Offset?
Financial modelling experts sometimes prefer Index or Offset for example where the table of data is not stable, and new data points are being continuously added (e.g. imported from existing financial results). With new data always being imported, but in a stable format, you can use Match or Count for example with Index or Offset to work out which rows and column you want to be in inside a continuously moving table. Although Excel gives us lots of choices for ‘plucking’ data out of a table (Vlookup, Hlookup, Index, Offset) Vlookup is the one perhaps most commonly used. You can do most of what you want to in financial modelling with that function, so make sure you’ve at least mastered Vlookup. That one’s really important to financial modelling career survival!
Coming up in the Excel financial modelling course extract
In the next lesson we will be looking at how can you use Excel True/False checks as a quicker shorter alternative to If functions in a financial model.
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering Excel functions.