# Address of last cell in range

To get the address of the last cell in a range, you can use the ADDRESS function together with ROW, COLUMN, and MAX functions. In the example shown, the formula in F5 is:

where **data** is the named range B5:D14.

*Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365, where dynamic array formulas are native.*

The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the last row and the last column used by the named range **data** (B5:D14).

To get the last row used, we use the ROW function together with the MAX function like this:

Because **data** contains more than one row, ROW returns an array of row numbers:

{5;6;7;8;9;10;11;12;13;14}

This array goes directly to the MAX function, which returns the largest number:

MAX({5;6;7;8;9;10;11;12;13;14}) // returns 14

To get the last column, we use the COLUMN function in the same way:

Since **data** contains three rows, COLUMN returns an array with three column numbers:

{2,3,4}

and the MAX function again returns the largest number:

MAX({2,3,4}) // returns 4

Both results are returned directly to the ADDRESS function, which constructs a reference to the cell at row 14, column 4:

=ADDRESS(14,4) // returns $D$14

If you want a relative address instead of an absolute reference, you can supply 4 for the third argument like this:

### CELL function alternative

Although it's not obvious, the INDEX function returns a reference, so we can use the CELL function with INDEX to get the address of the last cell in a range like this:

In this case, we use the INDEX function to get a reference to the last cell in the range, which we determine by passing total rows and total columns for the range **data** into INDEX. We get total rows with the ROWS function, and total columns with the COLUMNS function:

With the array provided as data, INDEX then returns a reference to cell D14:

INDEX(data,10,3) // returns reference to D14

We then use the CELL function with "address", to display the address.

*Note: The CELL function is a volatile function which can cause performance problems in large or complex workbooks.*

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

## Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.