January 28, 2021

How to get last value in a column in Excel

In this lesson we will learn how to get the value in the last non empty cell of a column in Excel. We will understand it with some simple examples as follows.

Below we have a table containing a list of students in a Motor training school and the miles driven by each one on a serial basis.

Column A contains student names and column B contains the miles driven. There are empty and non empty cells in column B as the students may or may not drive each day of the driving course.

Now first let’s find the last non empty cell in column B. In cell D2 we have used the below formula.

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

This formula is useful when we don’t want to provide the range of cells in a column. For the case we know the  cell range for a column to look for the last non empty value then we can add the cell references as follows to process the formula faster.

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

Here the source cell range we have provided as B2:B18. There the last value is 11 at cell B16 and the same has been found out with the formula as follows.

Let’s understand how the formula works.

  • B2:B18<>”” : This part returns a Boolean array. The array contains the value TRUE for the non empty cells in the range B2:B18 & FALSE for the empty cells in the same range. The array will look like {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}.
  • 1/(B2:B18<>””) : In this part, 1 is divided with the array as 1/{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}.  It will return an array of values 1 and errors, means the value TRUE will result 1(1 divided by 1) and the value FALSE will result #DIV/0! error(0 divided by 1). The array will look like {1;#DIV/0!;1;1;#DIV/0!;1}.
  • LOOKUP(2,1/(B2:B18<>””),B2:B18) : LOOKUP function searches for the value 2 here. If this value can’t be found, LOOKUP will match the next smallest value. In this case, the largest value in the lookup array is 1, so lookup will match the last 1 in the array and returns the corresponding value at the same position.

Now let’s learn another formula as follows which can also be used for the same purpose. In this case we have made couple of cells in column A as blank and we have used this formula in cell D2 to get the last non blank value in column A. This way we can see both of the above formulas work with any type of value, that is if the last value in a column is of type date or Boolean or any string or a decimal number, the formula will fetch the last value of the column irrespective of it’s data type.

=LOOKUP(2,1/(1-ISBLANK(A:A)),A:A)

This formula also works in the same way. Here the ISBLANK portion of the formula returns an array containing True or False value for each cell in column A, depending on whether that cell is blank or not. Each True or False value which is really a 1 or 0 in the array is subtracted from 1. There will be the resulting array of 1 & 0 as the True values produce 0(1-1) and the False values produce 1(1-0). After that the remaining calculation is same as the previous formula.

Check here to learn how to get the value of last non empty cell in a column in Google Sheet.

Leave a Reply

Your email address will not be published. Required fields are marked *