The item "Get the Last Nonblank Cell in An Excel Range" presented the following formula to get the last nonblank cell in the range B2:B25:
The example used all numeric data. If the data is either numeric or date, you can use the much simpler formula =LOOKUP(9^9, B2:B25). Better yet, define the name T for the range B2:B25 and use=LOOKUP(9^9,T). This is one of those rare opportunities to use LOOKUP.
This approach will not work with nonnumeric data. In that case you could use this array formula:
INDEX(T,MAX(ROW(T)* (T<>>""))).

Shane Devenshire

This is a distinctly unusual use for the LOOKUP command. Usually you pass LOOKUP a value and a range of sorted values, and it returns the largest value from the range that's less than or equal to the passed value. If the passed value is greater than all values in the array, LOOKUP normally returns the largest value. However, Excel's Help notes that if the array is not sorted, LOOKUP "may not give the correct value." In fact, it will simply return the last nonblank value—which is what we want.

The use of 9^9 (387420489) is arbitrary—it's just a large number that can be entered easily. If there's any chance that the numbers in question will be larger than this, simply choose a different large number like 10^300. 10^308 is the largest power of 10 Excel can handle; 2^1023 is the largest power of 2.

The second formula cleverly uses the fact that FALSE and TRUE correspond to 0 and 1, respectively, in Excel. It's an array formula, created as such by pressing Ctrl-Shift-Enter rather than just Enter. For each cell in the range T, the array formula returns the cell's row number if it's nonblank, or zero otherwise. The MAX function returns the largest such row, and the INDEX function returns the cell in the specified range that's at that row. Unfortunately, this formula assumes that the range starts in Row 1. A version that will work regardless of the range's location is a bit more complicated: =INDEX(T,MAX(ROW(T)*(T<>""))-ROW(T)+1). This is still better than the original OFFSET technique, which doesn't lend itself to using a named range.

If the items in the range are all nonnumeric, a variation on the LOOKUP technique will work. In place of 9^9, we need a text string that will be greater than any found in the range. A string of 255 z's should do the job, so use this formula: =LOOKUP(REPT ("z",255),T). Again, this only works for data that's entirely nonnumeric.