Tom’s Tutorials For Excel: The INDIRECT Function
INDIRECT function returns a range reference based on a text string, which can return a value associated with that range reference.
INDIRECT is a very versatile function, best explained by examples as shown below.
A simple example to get started: the formula in cell A3 is
INDIRECT behaves, depending on if its range reference argument is in quotes.
The formula in cell E5 (with quotes) is
The formula in cell E9 (without quotes) is
Here’s a creative way to use
INDIRECT with the
COLUMN function to copy the formula across a row, and transpose a vertical list.
The formula in cell A1 is
You can concatenate portions of a cell reference if it is split among cells.
The formula in cell is
You can also concatenate indirectly, if you know one reference, in this case column E with the formula deriving E6.
The formula in cell C4 is
You can indirectly refer to a named range. See a further explanation in the picture.
The formula in cell D1 is
You can nest
INDIRECT with operational functions such as
AVERAGE to produce more advanced results.
The formula in cell E2 is
can you suggest a way to use indirect formula across closed workbooks?
Hello Biray – –
Unfortunately, Excel does not provide for the INDIRECT function to read from a closed workbook. INDIRECT only works with references to open workbooks. Perhaps there is another approach to whatever you are working on, but if the workbook is closed, INDIRECT won’t be able to help you.
Many thanks for this… it is indeed a sophisticated and elegant technique, and no arrays are involved! :-)>
Best wishes in your endeavors