December 3, 2020

How to get last value in a column in Google Sheet

In this lesson we will learn how to get the value in the last non empty cell of a column in Google Sheet. 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.

=INDEX(B2:B18,MATCH(MAX(B2:B18),B2:B18,1))

We have used here INDEX, MATCH & MAX functions. And the source cell range we have provided as B2:B18. There the last value is 27 at cell B15 and the same has been found out with the formula as follows.

We have added couple of more values in column B and now we are using a different formula to get the last non empty cell in a column. Specially this formula is useful when we don’t want to provide the range of cells in a column. 

Now in cell D2 we have added the below formula and it fetched the value 19 which is currently the value in last non empty cell in column B.

=INDEX(FILTER(B:B,NOT(ISBLANK(B:B))),ROWS(FILTER(B:B,NOT(ISBLANK(B:B)))))

Here we have used INDEX, FILTER, NOT, ISBLANK and ROWS functions. The column range we have mentioned as B:B. In this case the last value will be dynamically fetched as and when any value is added at the last of the column since there is no last cell of the column specified in the range. The entire column has been mentioned as range in the formula hence there is provision to add more and more value in the column and the last non empty cell can be changed dynamically.

Now in our last example we have added all values in column B and here we will find the last mile driven for any specific student in cell A. As you have noticed there are multiple occurrences of same student names in column A. So here we have used a different formula in cell E2 as follows.

=INDEX(FILTER(B2:B,A2:A=D2),COUNTA(FILTER(B2:B,A2:A=D2)),1)

Here we have used INDEX, FILTER & COUNTA functions. In cell D2 we have added the student’s name for whom we need the last miles driven.

As you can see above there are 3 occurrences of the student’s name ‘Mike’ in column A. For the last occurrence, the value in column B is 24 for ‘Mike’. The same has been fetched by the formula as the last mile driven value for Mike in cell E3.

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

Leave a Reply

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