Tom’s Tutorials For Excel: The INDIRECT Function
The 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.
Example 1
A simple example to get started: the formula in cell A3 is =INDIRECT(B2).
Example 2
See how INDIRECT behaves, depending on if its range reference argument is in quotes.
The formula in cell E5 (with quotes) is =INDIRECT("A1").
The formula in cell E9 (without quotes) is =INDIRECT(A1).
Example 3
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 =INDIRECT("B"&COLUMN()+2).
Example 4
You can concatenate portions of a cell reference if it is split among cells.
The formula in cell is =INDIRECT(B1&A1).
Example 5
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 =INDIRECT("E"&A3).
Example 6
You can indirectly refer to a named range. See a further explanation in the picture.
The formula in cell D1 is =SUM(INDIRECT(B1)).
Example 7
You can nest INDIRECT with operational functions such as SUM and AVERAGE to produce more advanced results.
The formula in cell E2 is =SUM(INDIRECT("B2:B"&MATCH(E1,A:A,0))).
Hi,
can you suggest a way to use indirect formula across closed workbooks?
best regards,
Biray
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.
Hello Tom,
Many thanks for this… it is indeed a sophisticated and elegant technique, and no arrays are involved! :-)>
Best wishes in your endeavors
Thanks, Howard!!