Microsoft Office Tutorials and References

In Depth Information

Suppose that you have a worksheet that you update frequently by adding new data to columns. You may need a

way to reference the last value in column A (the value most recently entered). If column A contains no empty

cells, the solution is relatively simple and doesn't require an array formula:

=OFFSET(A1,COUNTA(A:A)–1,0)

This formula uses the COUNTA function to count the number of nonempty cells in column A. This value

(minus 1) is used as the second argument for the OFFSET function. For example, if the last value is in row 100,

COUNTA returns
100
. The OFFSET function returns the value in the cell 99 rows down from cell A1 in the

same column.

If column A has one or more empty cells interspersed, which is frequently the case, the preceding formula won't

work because the COUNTA function doesn't count the empty cells.

The following array formula returns the contents of the last nonempty cell in column A:

{=INDEX(A:A,MAX(ROW(A:A)*(A:A<>””)))}

You can, of course, modify the formula to work with a column other than column A. To use a different column,

change the column references from A to whatever column you need.

This formula does not work if the column contains any error values.

You can't use this formula, as written, in the same column in which it's working. At-

tempting to do so generates a circular reference. You can, however, modify it. For ex-

ample, to use the function in cell A1, change the references so that they begin with row

2 rather than the entire columns. For example, use A2:A1000 to return the last

nonempty cell in the range A2:A1000.

As I was finishing up this chapter, I found an alternate (non-array) formula that returns

the last value in a column. This formula returns the last nonempty cell in column A:

=LOOKUP(2,1/(A:A<>””),A:A )

The only problem is that I don't understand how it works, but it seems to be perfectly reliable and is

probably more efficient than the array formula. It differs from the array formula in one way: It ignores er-

ror values. So it actually returns the last nonempty non-error cell in a column.

Returning the last value in a row