Exploring Excel's Functions Part 2: ADDRESS() and INDIRECT()
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.