Functions for Finding Cell References: ADDRESS, OFFSET, INDIRECT

We often need to find cell references to use in our formulas. In this post, we will look at several such functions: ADDRESS, ROW, COLUMN, OFFSET, and INDIRECT. We mention INDEX(MATCH) but cover them in other posts.

For simplicity, in this post, we use the “A1” format of cell references throughout. The other option, the “R1C1” format, is obsolete. We’ve never come across anyone using it, so let’s ignore it.

ADDRESS(row_num, col_num, [abs_num]) returns a reference as text to a single cell in a worksheet

You can use the ADDRESS function to get the address of a cell in a worksheet, given specified row and column numbers. For example, ADDRESS(2, 3) returns $C$2. As another example, ADDRESS(70, 28) returns $AB$70. You can use other functions, such as the ROW and COLUMN functions, to provide the row and column number arguments for the ADDRESS function.

ADDRESS is like CELL(“address”, reference), but has more options. CELL uses “reference”, which itself is an address in A1 format, so what’s the point of it? The ADDRESS function uses row and column numbers. Hence it gives you a way to translate column numbers to column letters.

The ADDRESS function has the following arguments:

row_num (required): The row number to use in the cell reference.
col_num (required): The column number to use in the cell reference.
abs_num (optional): A number that specifies the type of reference. If 1 or omitted = Absolute (e.g., $A$1). 2 = Absolute row; relative column (A$1). 3 = Relative row; absolute column ($A1). 4 = Relative (A1).

If you need the address of a cell on another sheet, simply add it with an exclamation mark (!) before the cell reference, e.g., =”Sheet1!” & ADDRESS(1,3) will return Sheet1!$C$1.

ADDRESS Examples

Note that while the ADDRESS function has the option to add a sheet name (A8 above), it does not actually check that the sheet exists. Hence it is no better than just adding the sheet name as shown in cell A9.

Note that inserting rows above, or columns to the left, of an ADDRESS formula or the cell it references, does not alter the result of the formula. It will still return the same cell address, although that may no longer be the cell you want.

ROW([reference]) Returns the row number of a reference

The ROW function syntax has the following argument:

Reference (optional): The cell or range of cells for which you want the row number.

If reference is missing, the ROW function returns the row of its own cell.

If reference is a range of cells, and if ROW is entered as a vertical array, ROW returns the row numbers of reference as a vertical array.

Reference cannot refer to multiple areas.

The ROW function is not the same function as ROWS (plural):

ROWS(array) Returns the number of rows in a reference

The ROWS function syntax has the following argument:

Array (required): An array, an array formula, or a reference to a range of cells for which you want the number of rows.

ROW & ROWS Function Examples

COLUMN([reference]) Returns the column number of a reference

The COLUMN function syntax has the following argument:

Reference (optional): The cell or range of cells for which you want the column number.

If reference is missing, the COLUMN function returns the column of its own cell.

If reference is a range of cells, and if COLUMN is entered as a vertical array, COLUMN returns the column numbers of reference as a vertical array. How this is useful is unclear.

Reference cannot refer to multiple areas.

COLUMNS(array) Returns the number of columns in a reference

The COLUMNS function syntax has the following argument:

Array (required): An array, an array formula, or a reference to a range of cells for which you want the number of columns.

COLUMN & COLUMNS Function Examples

OFFSET(reference, rows, cols, [height], [width]) Returns a reference offset from a given reference

The OFFSET function syntax has the following arguments:

Reference (required): The reference on which to base the offset. It must refer to a cell or range of adjacent cells, otherwise OFFSET gives the #VALUE! error value.
Rows (required): The number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
Cols (required): The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
Height (optional): The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
Width (optional): The width, in number of columns, that you want the returned reference to be. Width must be a positive number.

Remarks

If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.

If height or width is missing, it is assumed to be the same height or width as reference.

OFFSET doesn’t actually move any cells or change the selection; it just returns a reference. OFFSET can be used with any function expecting a reference argument. For example, the formula SUM(OFFSET(C2,1,2,3,1)) calculates the total value of a 3-row by 1-column range that is 1 row below and 2 columns to the right of cell C2.

OFFSET Examples

INDIRECT(ref_text) Returns a reference indicated by text

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Argument:

Ref_text (required): A reference to a cell that contains a cell reference, a range name, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

Note External references are not supported in Excel Web App.

If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.

INDIRECT Examples

INDEX & MATCH

INDEX and MATCH, often used together as INDEX(MATCH)), are other powerful functions to refer indirectly to cells. They are well covered in these posts:

Use INDEX(MATCH) instead of an Excel LOOKUP Function

For a complex example, see Look up winners based on their scores: A Toastmasters contest in Excel

Functions for Finding Cell References: ADDRESS, OFFSET, INDIRECT