Lookup & Reference
Relative Row & Column Numbers, First & Last Row in Range (ROW / ROWS)
Convert absolute worksheet coordinates into 1-based relative table positions ROW(B5) - ROW($B$3) + 1 and calculate first/last row and column boundaries of any range.
Convert worksheet coordinates into table positions
For the table B3:D6, ROW(B5) returns worksheet row 5. Subtract ROW($B$3) and add 1 to obtain table row 3. The $ anchor stays fixed when copying the formula; it does not make the address immune to structural edits.
Offsets alone do not validate membership in the table: B2 gives relative row 0 and B7 gives 5. Check that row offsets lie between 1 and ROWS(B3:D6), and column offsets between 1 and COLUMNS(B3:D6), before using them for a single-cell lookup.
These examples test coordinate calculations on the supplied range, not automatic reference rewriting after inserting or deleting rows.
| Range Geometry Metric (Block B3:D6) | Formula | Evaluated Result |
|---|---|---|
| 1-Based Relative Row of B5 | ROW(B5) - ROW($B$3) + 1 | 5 - 3 + 1 = 3 (3rd table row) |
| 1-Based Relative Col of D5 | COLUMN(D5) - COLUMN($B$3) + 1 | 4 - 2 + 1 = 3 (3rd table col) |
| Last Worksheet Row of B3:D6 | ROW(B3) + ROWS(B3:D6) - 1 | 3 + 4 - 1 = 6 |
| Dynamic Cell at Relative (B5, D5) | INDEX(B3:D6, RelRow, RelCol) | Returns D5 (480) |
1-Based Relative Row & Column Position Inside an Offset Range
Subtracts anchor ROW($B$3) (or COLUMN($B$3)) and adds 1.
=ROW(B5) - ROW($B$3) + 1Retrieve the value at relative row 3, column 3
INDEX returns D5. Its value can change while the coordinate calculation stays the same.
=INDEX(B3:D6, ROW(B5) - ROW($B$3) + 1, COLUMN(D5) - COLUMN($B$3) + 1)Relative column of D5
The supplied B3:D6 range gives 3.
=COLUMN(D5)-COLUMN($B$3)+1First worksheet row
The supplied B3:D6 range gives 3.
=ROW(B3)Last worksheet row
The supplied B3:D6 range gives 6.
=ROW(B3)+ROWS(B3:D6)-1First worksheet column
The supplied B3:D6 range gives 2.
=COLUMN(B3)Last worksheet column
The supplied B3:D6 range gives 4.
=COLUMN(B3)+COLUMNS(B3:D6)-1Try the example
Use the original values or change them to test your understanding.