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)
Search JabSto ::




Custom Search