How to get Last Value in a Column in Google Sheets

There are multiple instances when you need to get the last value in a column in Google sheets. You can scroll down and look at the value but this is not a good idea when you have a huge dataset. For example, there’s a list of students in descending order of marks scored. You want to know the student who has scored the least. Let’s go over some methods to find the last value in a column. 

3 methods to get the last value in a column in Google Sheets: 

  • Using INDEX and COUNTA functions 
  • Using INDEX and MATCH functions for numerical values 
  • Using INDEX and MATCH functions for text values 

Method 1: Using INDEX and COUNTA functions  

Using the INDEX and COUNTA functions is a good idea when our dataset doesn’t have blank cells. 

Step 1: Identifying the range. 

The range is the column for which you need to find the last value.

In this case, it’s E.

Since cell E1 has the header, the range will be E2:E.

Step 2: Applying the function according to the syntax. 

We use the following formulae:

= INDEX(RANGE,COUNTA(RANGE))

In our example, we’d need to use the following formula:

= INDEX(E2:E,COUNTA(E2:E))

Here, the result is 28. 

This formula works well for a dataset with no blank cells. 

 To account for blank cells as well, let’s modify this to include filter. 

So syntax would be as follows: 

= INDEX(FILTER(Range, Range<>””),COUNTA(FILTER(RANGE, RANGE<>””)))

On the present data set, this is what we get: 

=INDEX(FILTER(E2:E, E2:E<>””),COUNTA(FILTER(E2:E, E2:E<>””)))


Method 2: Using INDEX and MATCH functions for numerical values 

Let’s use another set of functions to get the last value. This is good when you have a fair idea of the highest numerical value. For example, if you know that the number cannot exceed 100 or 900 or any other value. 

Step 1: Identifying the range. 

Range is the column for which you need to find the last value. 

In this case it is E. So our range becomes E:E.

Step 2: Applying the function according to the syntax. 

We use the following syntax, 

= INDEX( Range,MATCH(High value to compare value against it,Range)

In our example, this is as follows: 

= INDEX(E:E, MATCH(100^100, E:E)

We get the correct result. This formula works even if there are blank cells. 


Method 3: Using INDEX and MATCH functions for text values 

If you want to know the last value in a column with text values, we modify the syntax a bit.

Step 1: Identifying the range. 

The range’s the column for which you need to find the last value. 

In this case, it is E. So our range becomes E:E.

Step 2: Applying the function according to the syntax. 

We use the following syntax, 

= INDEX(Range, MATCH(“zzzzz”,Range))

Here, the value that the formula checks against is “zzzzz” because that’s the value that we are unlikely to encounter as the last letter of the alphabet. 

We get the correct result. This formula works even if there are blank cells. 

Conclusion 

Hope you found these methods helpful. Let us know if there’s anything else that we should add. 

Leave a Comment