Google Sheets ADDRESS: Build Dynamic References with ADDRESS
Master the Google Sheets ADDRESS function to build dynamic cell references, including sheet names, absolute vs relative addressing, and practical examples for dashboards and data workflows.
ADDRESS returns a text cell reference from a specified row and column, with optional sheet naming and A1/R1C1 styles. It enables dynamic references for INDIRECT and cross-sheet formulas. Mastering ADDRESS lets you build robust, reusable references for dashboards and data workflows.
About the GOOGLE SHEETS ADDRESS function
The google sheets address function is a powerful way to generate a text-based cell reference from a row and column. It is especially useful when building dynamic formulas for dashboards or cross-sheet lookups. According to How To Sheets, mastering ADDRESS unlocks robustness in complex spreadsheets and reduces hard-coded references. This section demonstrates the core usage with practical examples.
=ADDRESS(5, 3)This default form returns an absolute reference to row 5, column 3 (i.e., $C$5). To see how the addressing changes, try different abs_num values in the next examples:
=ADDRESS(5, 3, 2) // $C5 (absolute row, relative column)
=ADDRESS(5, 3, 3) // C$5 (relative row, absolute column)
=ADDRESS(5, 3, 4) // C5 (fully relative)ADDRESS syntax and parameters
The syntax of ADDRESS is:
ADDRESS(row, column, [abs_num], [a1], [sheet_text])- row: required. The row index to reference.
- column: required. The column index to reference.
- abs_num: optional. Controls absolute/relative anchoring:
- 1: absolute row and column ($A$1)
- 2: absolute row, relative column ($A1)
- 3: relative row, absolute column (A$1)
- 4: fully relative (A1)
- a1: optional. TRUE (default) uses A1 style; FALSE uses R1C1 style.
- sheet_text: optional. Name of the sheet to reference, e.g., "Data".
Examples:
=ADDRESS(1, 1) // $A$1
=ADDRESS(1, 1, 2) // $A1
=ADDRESS(1, 1, 3, TRUE) // A$1
=ADDRESS(1, 1, 4, FALSE, "Sheet1") // Sheet1!A1 (R1C1 with sheet)Absolute vs relative addressing with ADDRESS
Choosing the right abs_num value is essential when copying formulas. Absolute addressing keeps anchors in place, while relative addressing adapts as you drag or copy formulas across rows or columns. The four variants below illustrate how each setting affects the produced reference:
=ADDRESS(5, 3, 1) // $C$5
=ADDRESS(5, 3, 2) // $C5
=ADDRESS(5, 3, 3) // C$5
=ADDRESS(5, 3, 4) // C5Understanding this helps you design flexible templates where the same ADDRESS call can point to different cells based on its position in the sheet.
Cross-sheet references with ADDRESS
You can specify a sheet name to generate a cross-sheet reference. This is especially handy in dashboards that summarize data from multiple tabs. The result is a reference like Sheet2!$D$7. When you need a live value from that address, wrap the ADDRESS result with INDIRECT:
=INDIRECT(ADDRESS(7, 4, 1, TRUE, "Sheet2"))This returns the value from Sheet2, cell D7. Note that sheet names with spaces are automatically quoted by ADDRESS when used in the final reference.
INDIRECT and dynamic references using ADDRESS
ADDRESS returns a text string. To turn that string into an actual reference that can be used by other formulas, wrap it with INDIRECT. This combination is the primary mechanism for dynamic lookups across sheets. Example:
=INDIRECT(ADDRESS(2, 5)) // refers to E2 in the current sheetBeware: INDIRECT is a volatile function and will recalculate more often, which can impact performance in large workbooks.
Practical examples: dynamic VLOOKUP range using ADDRESS
A common use case is creating a dynamic lookup range without hard-coding sheet ranges. You can concatenate two ADDRESS results with a colon to form a range string, then feed it to INDIRECT for VLOOKUP. Example:
=VLOOKUP(A2, INDIRECT(ADDRESS(1, 1, 4, TRUE, "Sheet1") & ":" & ADDRESS(100, 4, 4, TRUE, "Sheet1")), 3, FALSE)This constructs the range A1:D100 on Sheet1 and looks up A2 in the first column, returning the third column value. The pattern can be adapted for INDEX/MATCH as well.
Common pitfalls and troubleshooting
- ADDRESS returns text. If you pass it directly to a function expecting a range, it will fail. Always wrap ADDRESS with INDIRECT when you need a live reference.
- If your sheet name contains spaces or special characters, ensure it is quoted correctly. ADDRESS handles quoting in the output, but INDIRECT requires valid syntax.
- Using ADDRESS with INDIRECT in large sheets can slow recalculation. Consider alternative patterns such as INDEX/MATCH when possible.
=IFERROR(INDIRECT(ADDRESS(1, 1, 4, TRUE, "Data")), "N/A")This IFERROR wrapper prevents #REF! when the address is invalid.
Steps
Estimated time: 15-20 minutes
- 1
Plan target addresses
Identify which cells or ranges you want to reference across sheets. Decide whether you need absolute or relative references for your dashboard.
Tip: Sketch a small map of where references will originate and where they will be used. - 2
Generate addresses with ADDRESS
Use ADDRESS to create text addresses for headers or data cells. Start with simple rows/columns to verify results.
Tip: Test multiple abs_num values to see how anchoring changes when copied. - 3
Convert to live references with INDIRECT
Wrap ADDRESS results in INDIRECT when you need a working reference for calculations or lookups.
Tip: Use IFERROR to handle invalid addresses gracefully. - 4
Build dynamic ranges for lookups
Concatenate ADDRESS outputs to form a range string and pass it to INDIRECT for functions like VLOOKUP or INDEX.
Tip: Keep ranges bounded to avoid excessive recalculation. - 5
Validate and optimize
Test with realistic data, watch for errors, and consider alternatives like INDEX/MATCH for performance.
Tip: Avoid overusing volatile functions in large sheets.
Prerequisites
Required
- Required
- Access to Google Sheets (web or mobile app)Required
- Basic knowledge of spreadsheet formulasRequired
Optional
- Optional: familiarity with INDIRECT and VLOOKUPOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected range | Ctrl+C |
| PastePaste into a cell | Ctrl+V |
| FindSearch within sheet | Ctrl+F |
FAQ
What does ADDRESS return in Google Sheets?
ADDRESS returns a text string that looks like a cell address, built from a row and column. It can include a sheet name and can output A1 or R1C1 style depending on parameters.
ADDRESS returns a text address that you can convert to a live reference with INDIRECT.
How do I use ADDRESS with INDIRECT?
Use ADDRESS to build a dynamic address, then wrap with INDIRECT to reference the cell value. For example, =INDIRECT(ADDRESS(2,1)) returns the value in cell A2.
Combine ADDRESS with INDIRECT to create dynamic references.
Can ADDRESS define a range?
ADDRESS returns a single cell address as text. To reference a range, build start and end addresses and join with ':' inside INDIRECT.
It returns a single address; use INDIRECT for ranges.
What about absolute vs. relative addressing?
abs_num controls anchoring; 1 is $A$1, 2 is $A1, 3 is A$1, 4 is A1. The sheet_text parameter names the sheet.
You control anchoring with abs_num.
Is ADDRESS volatile?
ADDRESS itself is not volatile, but when used with INDIRECT, it becomes volatile because INDIRECT recalculates with dependencies.
It depends on how you use it.
The Essentials
- ADDRESS creates dynamic cell addresses
- Use INDIRECT to turn ADDRESS text into a reference
- Combine with sheet_text for cross-sheet references
- Be aware of performance implications with volatile functions