Exceljet

Quick, clean, and to the point

Get value of last non-empty cell

Excel formula: Get value of last non-empty cell
Generic formula 
=LOOKUP(2,1/(A:A<>""),A:A)
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 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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables