You’ve got a column with a bunch of rows of values. And maybe rows get added or removed from time to time. So, the location of the last (or perhaps latest) value is always changing. Here’s how you find that last value and grab it so you can do stuff with it.
In our example, we’ll use a table that lists “guests” along with the date of each guest’s visit (sort of like the type of thing that you might have at a bed and breakfast or a formal party or something).
There’s a handy function call INDEX that will give you the contents of a cell when you specify a range and then the row number that you want. We know the range we want to look at, right? Our range is A2:A, i.e. the list of guest names. The challenge is finding the row number of the last row that has a name in it.
So, let’s start by putting the INDEX function into a formula in cell C2.
=INDEX()
We know the range, right? Our range is the list of guest names, i.e. cells A2:A. (You won’t specify a number after the second “A” because the names on our list could expand infinitely. So, we want don’t want to specify a limit to the number of rows Google Sheets should look at.)
=INDEX(A2:A)
Next, we need to specify the “index” of the last row in our range.
Hmm… Suppose that that we’ve got 5 names in our range A2:A. The number of names might be equal to the index of the last row. If you’ve got 5 rows of names, the index of the last row could, in theory, be index number 5. If that’s the case, we can use the COUNTA function to count the number of values in our range. And that should tell us the index of the last cell.
=INDEX(A2:A,COUNTA(A2:A))This works if you have no empty rows in between names. But… there’s sort of a saying in the development world that goes something like, “If people can do it, they will.” The moral of the story is, because it’s possible for there to be an empty row, it’s best to account for that possibility.
In our example table, you’ll notice that we’ve got an empty row between names. So, if we use our formula as is, the number of rows with values won’t match the index of the last row in our range. So, we won’t get the cell we want.
To solve this problem we’ll use the handy function FILTER. We can use this function to remove all empty rows and get an array of nothing but rows with values. (By the way, the FILTER function is so handy that I use it in almost everything I do.)
Let’s apply the FILTER function to our A2:A ranges so that each range only contains cells with values.
=INDEX(FILTER(A2:A,A2:A<>""), COUNTA(FILTER(A2:A,A2:A<>"")))
Yes! Now our INDEX function is looking at a range of nothing but names (i.e. no empty rows). And, in the second parameter of the INDEX function, the number of values in the range matches the index of the last row in that range (again because there are no empty rows in the range).
Excellent! We’ve now got a (virtually) foolproof system for getting the last entry in our guest list.
Hi! Very good article and very good explained.
In case you don’t want to get only the last row, but the last 3 rows for example, how should it be done?
I stay tuned for new news.
Thanks!
Try something like this:
=QUERY(SORT({SEQUENCE(3,1,3,-1),ARRAY_CONSTRAIN(SORT(Sheet1!A2:A,ROW(Sheet1!A2:A)*N(Sheet1!A2:A<>""),FALSE),3,1)}),"SELECT Col2",0)You can see it in action here.
MATE! Thank you so much for the formula and associated explanation. Now I can get my running balance transferred to the top of the screen, instead of having to scroll to the bottom of the sheet.