Lookup & Reference
Get address of first and last cell in a range (ADDRESS, ROW, ROWS, COLUMN & COLUMNS)
Return the top-left C3, bottom-right E6, or full $C$3:$E$6 address text for a rectangular range.
Calculate the corners of a rectangular range
For the single rectangular range C3:E6, MIN reduces the row and column coordinates to the top-left position. Adding ROWS(range)-1 and COLUMNS(range)-1 yields the bottom-right position. The formulas describe the range boundary, not the first or last nonempty cell.
ADDRESS produces text. It does not select cells, set a print area, create a hyperlink or return a live reference. Without a sheet-name argument the coordinate is local to a worksheet. These examples do not handle disjoint multi-area references.
The shown patterns use ordinary cell references and scalar MIN results. Spreadsheet versions can differ in array evaluation; test the intended version rather than assuming that all versions spill or calculate identically.
| Range Coordinate Target | Formula Pattern (rng = C3:E6) | abs_num Mode | Returned String / Value |
|---|---|---|---|
| Top-Left First Cell (Relative) | ADDRESS(MIN(ROW(rng)), MIN(COLUMN(rng)), 4) | 4 (Relative) | "C3" |
| Bottom-Right Last Cell (Relative) | ADDRESS(MIN(ROW)+ROWS-1, MIN(COL)+COLS-1, 4) | 4 (Relative) | "E6" |
| Full Absolute Range String | First(abs=1) & ":" & Last(abs=1) | 1 (Default $A$1) | "$C$3:$E$6" |
| Bottom-Right Cell Value | INDIRECT(LastAddress) | Dereferenced E6 | 940 (or updated E6) |
Address of FIRST (top-left) cell in a range
Pass abs_num = 4 for clean relative coordinates ("C3") or omit abs_num for absolute "$C$3".
=ADDRESS(MIN(ROW(C3:E6)), MIN(COLUMN(C3:E6)), 4)Address of LAST (bottom-right) cell in a range
Adds ROWS(rng) - 1 and COLUMNS(rng) - 1 to the top-left origin so single-cell ranges (ROWS = 1, COLUMNS = 1) still return the exact cell without off-by-one drift.
=ADDRESS(MIN(ROW(C3:E6)) + ROWS(C3:E6) - 1, MIN(COLUMN(C3:E6)) + COLUMNS(C3:E6) - 1, 4)Try the example
Use the original values or change them to test your understanding.