Get value of last non-empty cell

If you need find the value of the last non-empty cell in a row or column, you can use the LOOKUP function in this surprisingly compact formula. As an additional bonus, this formula is not an array formula, and not volatile.

How this formula works

The key to understanding this formula is to recognize that the lookup_value of 2 is deliberately larger than any values that will appear in the lookup_vector.

1. The expression A:A<>"" returns an array of true and false values: {TRUE, FALSE, TRUE,...}.
2. The number 1 is then divided by this array and creates a new array composed of either 1's or divide by zero errors (#DIV/0!): {1,0,1,...}. This array is the lookup_vector.
3. When lookup_value can't be found, LOOKUP will match the next smallest value.
4. In this case, the lookup_value is 2, but the largest value in the lookup_array is 1, so lookup will match the last 1 in the array.
5. LOOKUP  returns the corresponding value in result_vector (i.e. the value at the same position).

Dealing with errors

If there are errors in the lookup_vector, particularly if there is an error in the last non-empty cell, this formula needs to be adjusted. This adjustment is needed because <>"" criteria will return an error itself if a cell contains an error. To workaround this problem, use ISBLANK with NOT:

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

Last numeric value

To get the last numeric value, you can add the ISNUMBER function like this:

=LOOKUP(2,1/(ISNUMBER(A1:A100)),A1:A100)

Position of the last value

If you want to get the position (in this case row number) of the last value, you can try a formula like this:  

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

Here we feed the row numbers of the same range into lookup for the result vector and get back the row number of the last match.