Quick, clean, and to the point

Get value of last non-empty cell

Excel formula: Get value of last non-empty cell
Generic formula 

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.

  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:


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.

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.