INDIRECT Function in Excel
How to Use Excel’s INDIRECT Function
Excel’s INDIRECT function is a brilliantly simple way of pulling data from multiple tabs and putting this together in one place.
The idea is that the user provides the INDIRECT function with a cell reference and it will return the contents of that cell. This function will save time, improve accuracy and create flexibility!
When you start your indirect formula in Excel, the text in your formula bar will look like this:
=INDIRECT(ref_text, [A1])
Arguments in square brackets are always optional – since “[A1]” adds nothing to our discussion here we’ll ignore it, leaving us with only the “ref_text” argument.
There are a vast number of applications for this function, but rather than attempting to cover the full breadth I shall focus on my favorite application: building a comps sheet.
I’ve pulled together some financial information for three companies, which I will use to summarise the enterprise multiples in my dashboard.
You could, of course, approach this in a very manual way, creating a link to each company’s respective EV, EBIT and EBITDA, however, this is painfully time-consuming, opens you up to human error and isn’t very flexible when adding more comparable companies.
The INDIRECT function is our friend here.
Notice that for each individual company the model is laid out identically, for example, the Enterprise Value always appears in cell B7. So, if I want to return the EV for Mars on my dashboard, the reference I need to feed into the indirect function is:
Mars!B7
Notice that Excel requires an exclamation mark between the sheet name and the cell reference. This has nothing to do with the INDIRECT function as such but is a general syntax rule within Excel.
Let’s build the reference above into the INDIRECT function:
=INDIRECT(C$1&”!”&$A2)
If you take a look at cell A2 you will see that I’ve populated this with the text “B7”, which refers to the location of the Enterprise Value in the individual comparable company sheet. You’ll also see that I’ve used ampersands and quote marks either side of the exclamation mark and used dollar signs to lock references where appropriate – this is general Excel syntax.
If you replicate what I’ve done and have a play around with the structure, you’ll see how easy it is to add more comparable companies to the model, change what company you are linking to or adjust the order. Just change the names in row 1!