ADDRESS
Creates a cell reference as text from row and column numbers.
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])Arguments
row_numRow numbercolumn_numColumn numberabs_numReference type (1-4)(optional)a1TRUE for A1 style(optional)sheet_textSheet name(optional)
=ADDRESS(1,1)Absolute reference
=ADDRESS(1,1,4)Relative reference
=ADDRESS(5,3,1,TRUE,"Sheet2")With sheet name
- •abs_num: 1=absolute, 4=relative
- •Use INDIRECT to convert to actual reference
- •Useful for dynamic references
- •Forgetting that ADDRESS returns text, not an actual cell reference - the result looks like a cell address but can't be used directly in a formula the way a real reference can, without wrapping it in INDIRECT
- •Mixing up the abs_num argument's meaning - it controls absolute vs. relative referencing (1 for fully absolute, 4 for fully relative, and other values for mixed), and getting the wrong number produces unexpected $ symbols or their absence
- •Not specifying sheet_text when a reference to a different sheet is needed, resulting in an address that only makes sense within the current sheet
INDIRECTConverts the text ADDRESS produces into an actual, usable cell reference, since ADDRESS alone only returns a text string.CELLRetrieves information about an existing cell, like its actual address, format, or contents, rather than constructing an address from row/column numbers like ADDRESS.ROWOften supplies the row number argument dynamically when building an address with ADDRESS.Why can't I use ADDRESS's result directly as a cell reference?
ADDRESS returns a text string that looks like a cell address, not an actual reference. Wrap it in INDIRECT, like INDIRECT(ADDRESS(...)), to turn it into a usable reference.
What does the abs_num argument control?
Whether the resulting address is absolute or relative - 1 makes both row and column absolute (like $A$1), 4 makes both relative (like A1), and 2 or 3 mix the two.
How do I include a sheet name in the address ADDRESS builds?
Add the sheet_text argument, like ADDRESS(1,1,1,TRUE,"Sheet2"), which produces "Sheet2!$A$1".
Need to translate a formula using ADDRESS?
Use our translator to convert your complete formula