Microsoft Office Tutorials and References

In Depth Information

**Array formula method**

Array formula method

The following array formula returns the contents of the last non-empty cell in the first 500 rows of

column B, even if column B contains blank cells:

=INDEX(B:B,MAX(ROW(B:B)*(B:B<>””)))

Press Ctrl+Shift+Enter (not just Enter) to enter an array formula.

Note

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

column, change the four column references from B to whatever column you need.

You can’t use this formula, as written, in the same column in which it’s working. Attempting to do so

generates a circular reference. You can, however, modify it. For example, to use the function in cell

B1, change the references so that they begin with row 2 rather than the entire columns. For example,

use B2:B1000 to return the last non-empty cell in the range B2:B1000.

The following array formula is similar to the previous formula, but it returns the last non-empty cell in

a row (in this case, row 1):

=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>””)))

To use this formula for a different row, change the three 1:1 row references to correspond to the

correct row number.

Standard formula method

The final method uses a standard (non-array) formula, and is rather cryptic. This formula returns the

last non-empty cell in column B:

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

This formula ignores error values, so if the last non-empty cell contains an error (such as #DIV/0!), the

formula returns the last non-empty, non-error cell.

The following formula returns the last non-empty, non-error cell in row 1:

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