Excel’s index function

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.

Please download the Excel spreadsheet for an example of the Image showing how to use the Index function in Excel

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.

Image comparing Excel Index vs. Vlookup functions

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.

Image showing how to use the Excel Match function as an input into the Index function

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.

Image showing how to use the Excel Count function as an input into the Index function

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.

Image showing how to use the Excel Offset function

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.

 

 

2 comments so far:

...join the conversation...

Your email address will not be published. Required fields are marked *

  1. This Cource is Very good.i like it.
    i want to learn also EXCEL VBA Macro…..

    Reply