The INDIRECT Function in Microsoft Excel

The INDIRECT function in Excel is used when you want to reference a cell to get input related to another cell. Sounds strange at first, but it is a clever tool that can provide some flexibility in your financial modeling but only in the rarest of occasions. This formula can provide the financial modeler with the ability to more easily interpret changing data all in one cell, without the need to develop a very long string of formulas and references in a cell, but for most financial model practitioners, you may never need to use it.

The form of the INDIRECT function is =INDIRECT(ref,a1), where "ref" is the cell to which you are referring and "a1" represents a true or false option – if false, the function will interpret the data on an RiCi-style basis and if true or omitted, the data is interpreted as a letter-number style (also known as A1 style).

For example, let's assume that the ultimate data the model is seeking is contained in the area defined by G3:G7 and that these are numbers range from 1 to 100. Further assume that the headings in the area defined by F1:F7 are Mon, Tues, Wed, Thurs and Fri. So this could be unit sales per day, lost items per day, ending inventory or whatever specific analysis is warranted. If you wanted to know how many sales took place on Friday, you would enter =INDIRECT(H7), with H7 containing "G7" and it would go to the value in G7. This is pretty cool, but not entirely useful. There are many ways to accomplish this without using INDIRECT, but this is merely an example of how to incorporate the formula.

I can tell you that I have probably used the INDIRECT formula once or twice in my entire career. That does not mean that it is totally useless, it just means that for most financial modeling tasks there is no need to incorporate it. In particular, most lookup or searching situations can be accomplished by the more familiar formulas including LOOKUP, VLOOKUP, HLOOKUP, SEARCH or FIND. This is not to say that you will never use it, for there may be that rare opportunity to do so. The point to this story is that you will probably never need to incorporate this formula in your normal, day-to-day financial modeling tasks.