The “last used cell” in Google Sheets refers to the cell with the highest row or column number that is not empty within a specified range. It is not related to the time the cell was last edited. There are no built-in functions in Google Sheets to directly find the address of the last used cell within a range. However, here are two dynamic formulas you can use: one for vertical datasets and another for horizontal datasets.

To find the address of the last used cell in a vertical dataset, use the following formula:

=LET(range, you_range_here, test1, IFNA(BYCOL(range, LAMBDA(col, CELL("row", INDEX(col, XMATCH(TRUE, col<>"", 0, -1)))))), test2, IFNA(BYCOL(range, LAMBDA(col, CELL("address", INDEX(col, XMATCH(TRUE, col<>"", 0, -1)))))), fnl, XLOOKUP(MAX(test1), test1, test2, "", 0, -1), fnl)

To find the last used cell in a horizontal dataset, use this formula:

=LET(range, you_range_here, test1, IFNA(BYROW(range, LAMBDA(row, CELL("col", INDEX(row, 0, XMATCH(TRUE, row<>"", 0, -1)))))), test2, IFNA(BYROW(range, LAMBDA(row, CELL("address", INDEX(row, 0, XMATCH(TRUE, row<>"", 0, -1)))))), fnl, XLOOKUP(MAX(test1), test1, test2, "", 0, -1), fnl)

In both formulas, replace your_range_here with the range you want to apply these formulas.

Finding the Address of the Last Used Cell in a Vertical Dataset

In the following example, I have sales data in the range A1:D as shown below:

The following formula returns $B$6, which is the address of the last used cell in this range:

=LET(range, A1:D, test1, IFNA(BYCOL(range, LAMBDA(col, CELL("row", INDEX(col, XMATCH(TRUE, col<>"", 0, -1)))))), test2, IFNA(BYCOL(range, LAMBDA(col, CELL("address", INDEX(col, XMATCH(TRUE, col<>"", 0, -1)))))), fnl, XLOOKUP(MAX(test1), test1, test2, "", 0, -1), fnl)

If you want to find the last used cell in any specific column within this range (e.g., column D), replace A1:D with D1:D.

Formula Breakdown

The core of the formula lies in the combination of XMATCH, INDEX, and CELL functions.

XMATCH(TRUE, col<>"", 0, -1)

This XMATCH formula matches TRUE in an array that contains TRUE or FALSE values (indicating non-empty cells) from bottom to top and returns the position of the first match in the array.

We use this function within BYCOL, so col refers to each column in the range A1:D. This means the test is performed on each column, and the formula returns the relative position of each column’s last non-empty cell.

The INDEX function then returns the value of those cells.

There are two BYCOL formulas named test1 and test2 (defined using LET). In both BYCOL formulas, we use the INDEX result. The CELL function in test1 returns the row numbers, while the CELL function in test2 returns the cell addresses.

Finally, the XLOOKUP function:

XLOOKUP(MAX(test1), test1, test2, "", 0, -1)

This formula looks for the maximum value in test1 (which contains the row numbers) and returns the corresponding cell address from test2, giving you the address of the last used cell in the specified range.

Finding the Address of the Last Used Cell in a Horizontal Dataset

Although people commonly use vertical datasets, they sometimes use horizontal datasets.

For example, consider the following dataset in A3:Z6, where the first column contains employee names and the rest of the range represents their attendance.

The following formula will return the address of the last used cell in the range A3:Z6:

=LET(range, A3:Z6, test1, IFNA(BYROW(range, LAMBDA(row, CELL("col", INDEX(row, 0, XMATCH(TRUE, row<>"", 0, -1)))))), test2, IFNA(BYROW(range, LAMBDA(row, CELL("address", INDEX(row, 0, XMATCH(TRUE, row<>"", 0, -1)))))), fnl, XLOOKUP(MAX(test1), test1, test2, "", 0, -1), fnl)

If you want to find the address of the last used cell in a particular row, such as A3:Z3, simply replace A3:Z6 with A3:Z3. It’s that easy!

How Does This Formula Differ from the Vertical Data Formula?

Here are the key differences between the formula that returns the cell address of the last used cell for horizontal and vertical datasets:

1. XMATCH Function: In the horizontal formula, XMATCH matches TRUE in each row and returns the position of the last non-empty cell. In the vertical formula, it matched TRUE in each column.
2. BYROW vs. BYCOL: The horizontal formula uses the BYROW function, while the vertical formula uses BYCOL.
3. INDEX Function: The horizontal formula’s INDEX offsets columns to return values, whereas the vertical formula’s INDEX offsets rows.
4. CELL Function: In the horizontal formula, CELL returns column numbers, while in the vertical formula, it returns row numbers.

Resources

• Find the Last Non-Empty Column in a Row in Google Sheets
• Vlookup to Get the Last Non-blank Value in a Row in Google Sheets
• Count From the First Non-Blank Cell to the Last Non-Blank Cell in a Row in Google Sheets
• Get the Header of the Last Non-blank Cell in a Row in Google Sheets
• Get the Headers of the First Non-blank Cell in Each Row in Google Sheets
• XMATCH First or Last Non-Blank Cell in Google Sheets
• Excel: XLOOKUP for First and Last Non-Blank Value in Row
• Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel