Get value of last non-empty cell

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.