Find the Cell Address of a Last Used Cell in Google Sheets

0
55

There is no built-in functions to find the cell address of a last used cell in Google Sheets. But I’ve two custom formulas to do this. Keep one thing in mind if you opt to use any of them. Both formula may fall short of returning a perfect result if there is any blank cell in the ‘primary’ column in the range. I’ll explain it.

Primary Column:

In your data range there may be multiple columns. For example a range like A1:G has 7 columns and infinitive rows. In this, you can set any column as primary in my formula. For example, you can use Column A. The formula may revolve around this column and blank cells in this column can return wrong results. So choose a column that does not contain blank cells in your range.

This formula is Different from what you may think!

Normally with a lookup formula we can find the cell address of a lookup value. But here the case is different. We are here to find the cell address of a last used cell not cell address of a search key found.

How to Find the Cell Address of a Last Used Cell in Google Sheets

Formula 1:

I’ve used three functions here to find the cell address of a last used cell in a range. The functions are CELL, INDEX and COUNTA. But the main function in this formula is of course the INDEX.

Formula Explanation:

First let me explain the role of Index function here. With the Index function my aim is to return the last cell value. From this value we can find the last cell address.

Syntax: INDEX(reference, [row], [column])
Formula: INDEX(A1:G,countA(A1:A),cell(“col”,G1))

I am sure that the colour pattern makes it easy for you to understand the elements in this formula as well as me to explain.

Here we have already the “reference” to use in Index and it’s A1:G. Now we want to find the last row number in our data range.

Here I’ve used CountA function in Column A which is my primary column here. Of course CountA has an edge over the Count function as it accepts both number as well as text in count.

It’s easy to find the column number as we know our last column in the data range is column G. You can directly put 7 as G represents column number 7. Alternatively the Cell formula returns Column number of G. Actually here the formula is like;

Formula: INDEX(A1:G,9,7)
Result: 99 (the last cell value in the range in G9.

You can use the above Index formula to return the last cell value in a range. Remember! not last used cell address.

Now we can use the Cell function “address” info type to return the address of this cell containing the value 99.

Result: \$G\$9
Note: When you copy and paste this formula on your sheet, re type the double quotes.

Formula 2

Here is one more formula to find the Cell Address of a last used cell in Google Sheets. This’s an entirely different approach. As you can see the main functions here in use are ADDRESS, ARRAYFORMULA, MIN, IF and ROW.

The below formula is the main part here and here my primary column is G. So any blank cell in this column possibly return wrong result.

Formula: =ArrayFormula(MIN(IF(G2:G=””,ROW(G2:G)-ROW(G2)+1)))
Result: 9

Formula Explanation:

When you us this formula independently, after removing the MIN, for example in H2, it may return serial number 1,2,3 starting from H2 to downwards. But the IF logical function may force the formula to put serial number only in H10. Because the formula only return numbers wherever the cell value in G is blank. That means it only return the number 9 in H10 as G10 is blank and in all the other cells, it returns FALSE. The MIN function in this range returns the number 9.

If you have still doubt, I’ve a detailed tutorial on this. Please check that.

So the formula is equal to;