Get value of last non-empty cell

=LOOKUP(2,1/(A:A<>""),A:A)
To 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.
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.
- The expression A:A<>"" returns an array of true and false values: {TRUE, FALSE, TRUE,...}.
- 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.
- When lookup_value can't be found, LOOKUP will match the next smallest value.
- 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.
- 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:
Last numeric value
To get the last numeric value, you can add the ISNUMBER function like this:
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:
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.
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.