Get value of last non-empty cell

Get value of last non-empty cell

Generic formula 

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

Related formulas 

Get date associated with last entry

Get last entry by month and year

Get first non-blank value in a list

Lookup latest price

Last row in numeric data

Last row in text data

Explanation

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 pided by this array and creates a new array composed of either 1's or pide 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.

Get value of last non-empty cell

Generic formula 

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

Related formulas 

Get date associated with last entry

Get last entry by month and year

Get first non-blank value in a list

Lookup latest price

Last row in numeric data

Last row in text data

Explanation

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 pided by this array and creates a new array composed of either 1's or pide 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.