INDIRECT Function
Converts a text string into a cell reference
Converts a text string into a cell reference
The INDIRECT function is categorized under Lookup and Reference functions. It will convert a text string into a cell reference. For example, if we type the reference A1 in a formula, Excel will understand that it refers to cell A1. However, if the cell reference given takes the form of a text string, we need to use the INDIRECT function.
In financial analysis, the function is useful when we import data from an external source and we want to convert a reference assembled as text into a proper reference. In other words, the INDIRECT function helps us to create an Excel dynamic cell reference or range reference rather than manually entering the references into our Excel formulas.
=INDIRECT(ref_text, [a1])
The INDIRECT function uses the following arguments:
When the a1 argument is omitted, it takes on the default value “TRUE.” When a1 is set to FALSE, INDIRECT will created an R1C1-style reference.
To understand the uses of the function, let us consider an example:
If we want to create an array of numbers like {10;20;30;40;50}, we can use a formula based on the ROW and INDIRECT functions. The technique can be used in array formulas that need a numeric array for processing of some kind.
Suppose we are given the following data:
The formula to use is:
After the formula is typed, we need to use F9 to see the array. We get the result below:
Remember that when the formula is entered into a single cell, Excel will display only the first item in the array. As mentioned above, we need to use F9 in the formula bar to see the actual array result. Ideally, we use this formula inside a larger array formula, entered with the Control + Shift + Enter command.
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your Excel modeling. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!