Lookup & Reference
Get Cell Value & Coordinate String at Given Row and Column (INDEX + ADDRESS)
Retrieve any cell’s exact value without volatile INDIRECT by pairing INDEX(B2:D5, G2, G3) with ADDRESS(Row_Offset + G2, Col_Offset + G3, 4) for audit trail reporting.
Why non-volatile INDEX(Range, Row, Col) beats INDIRECT(ADDRESS(...)) in shared financial models!
Many spreadsheet tutorials chain INDIRECT(ADDRESS(Row, Col)) just to read a cell by numeric row and column coordinates—turning a fast direct lookup into a single-threaded volatile formula that recalculates on every unrelated edit! When your data matrix sits in B2:D5, non-volatile INDEX(B2:D5, G2, G3) fetches the exact intersection directly in O(1) time while tracking row/column insertions cleanly!
Whenever an audit schedule also needs the human-readable worksheet coordinate string (e.g., "C4" for Matrix Row 3, Col 2 anchored at B2 where row offset = 1 and col offset = 1), ADDRESS(1 + G2, 1 + G3, 4) formats the relative A1 reference without invoking INDIRECT!
| Matrix Coordinate (G2 Row, G3 Col in B2:D5) | Worksheet A1 Coordinate | INDEX(B2:D5, G2, G3) Value | Out-of-Range Protection |
|---|---|---|---|
| Row G2 = 3, Col G3 = 2 | ADDRESS(4, 3, 4) = "C4" | $18,400 | Within 4×3 matrix bounds |
| Row G2 = 1, Col G3 = 3 | ADDRESS(2, 4, 4) = "D2" | $15,900 | Top-right Q3 cell |
| Row G2 = 6, Col G3 = 2 (> 4 Rows!) | Outside B2:D5 | "Coordinates Outside Matrix Bounds" | ROWS/COLUMNS pre-guard |
Retrieve Matrix Cell Value by Relative Row G2 & Column G3 (Bounds-Guarded INDEX)
Validates 1 <= G2 <= ROWS(B2:D5) and 1 <= G3 <= COLUMNS(B2:D5) before indexing into B2:D5.
=IF(OR(G2 < 1, G2 > ROWS(B2:D5), G3 < 1, G3 > COLUMNS(B2:D5)), "Coordinates Outside Matrix Bounds", INDEX(B2:D5, G2, G3))Return Relative Worksheet Cell Address String via ADDRESS(Row, Col, 4)
Converts matrix coordinates (G2, G3) anchored at B2 (row offset +1, col offset +1) into relative A1 notation (abs_num = 4).
=IF(OR(G2 < 1, G2 > ROWS(B2:D5), G3 < 1, G3 > COLUMNS(B2:D5)), "Coordinates Outside Matrix Bounds", ADDRESS(1 + G2, 1 + G3, 4))Try the example
Use the original values or change them to test your understanding.