Exploring Excel's Functions Part 2: ADDRESS() and INDIRECT()

Excel functions might seem a bit complex, but sometimes there's no avoiding a complicated formula to achieve the worksheet results you need. The ADDRESS() and INDIRECT() functions can provide you with flexibility that is not available any other way. Continuing Peter Aitken's series on Excel's advanced functions, this article explores the INDIRECT() and ADDRESS() functions and shows you how they can be used together to make your Excel life easier.

From the author of

From the author of

Excel functions might seem a bit complex, but sometimes there’s no
avoiding a complicated formula to achieve the worksheet results you need. The
ADDRESS() and INDIRECT() functions can provide you with flexibility that is not
available any other way.

This is the second in a series of articles that take a detailed look at some
of Excel’s advanced functions. Today we look at the INDIRECT() and
ADDRESS() functions and see how they can be used together.

Don’t Be So Direct

The INDIRECT() function lets you get the value in a cell by means of an indirect
reference. Indirect reference means that you refer to the address
of the cell rather than to the cell itself. Thus, INDIRECT(B2) does not return
the value in cell B2, but rather the value in the cell whose address is in
cell B2. So, if cell B2 contains the text "H5", the above function
will return the value in cell H5.

By default, INDIRECT()looks for A1 style addresses. If you are using R1C1
style addresses that indicate both the row and column by number, include FALSE
as the second argument to the function. Some things to be aware of:

If the referenced cell contains text that is the name of a single-cell
named range, INDIRECT() returns the contents of that named cell.

If the referenced cell is blank, contains a number, or contains text that
does not refer to a named range, INDIRECT() returns the error message #REF.

What if the referenced cell contains the name of a range with more than one
cell? Used alone in this situation, INDIRECT() does not return useful data.
Used with other functions, however, it can be very useful. For example, suppose
that the name Sales refers to the range B2:B20, and that cell F10 contains
the text Sales. Then the following formula will return the sum of cells B2:B20:

=SUM(INDIRECT(F10))

You can use INDIRECT() with an argument that is quoted text; for example,
INDIRECT("A4") rather than INDIRECT(A4). There’s a subtle but
important difference between these two, with the former returning the contents
of cell A4, and the latter returning the contents of the cell whose address
is in cell A4. This can be useful when you want a formula to always refer to
the same cell. Even with absolute cell addresses, the reference to the cell
will be updated if the cell is moved with the Cut command or if its location
changes because rows or columns are inserted or deleted. By using INDIRECT()
with a quoted cell address, as in the first example above, a formula will always
refer to that cell regardless of any worksheet changes. For example, this formula
will always sum cells C4 and C5:

=INDIRECT("C4")+INDIRECT("C5")

You can also use INDIRECT() with an argument that is part text and part cell
reference. Look at this example, which contains the text "D" and
the cell reference $A$6 combined with the concatenation operator ("):

INDIRECT("D""$A$6)

If cell A6 contains the number 1, this function will return the contents of
cell D1; if it contains 99, the function will return the contents of cell D99.
You can also combine two cell references like this:

INDIRECT(A1"A2)

If cell A1 contains the letter D, and cell A2 contains the number 5, the function
will return the value in cell D5.

In addition to creating a permanently fixed cell reference, as described above,
INDIRECT() has other uses. For example, when used in conjunction with the ROW()
function, which returns the row number of a cell reference, you can create
a list of sequential integers, something that is needed in various scenarios
such as array formulas. Let’s look at the steps required to make a list
of integers from 1 to 20. This requires entering the formula as an array formula.
If you are not familiar with this, just follow these directions:

Drag the mouse to select the 20 cells in which you want the list; for example,
cells A1:A20.

Type in the formula =ROW(INDIRECT("1:20"))

Press Ctrl+Shift+Enter to complete the formula.

You’ll now see the integers 1 through 20 in the selected cells. Couldn’t
we have done the same thing with the formula =ROW("1:20")? Yes, but
(as before) the references would be subject to changes if someone added one
or more new rows above the range. Using INDIRECT() prevents this.

We’ll see another use for INDIRECT() in the following section on the
ADDRESS() function.