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.

- 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:

### 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.