**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!!