Contact
12 programs
Explore the site

ADDRESS Function in Zoho Sheet: Build Cell References as Text

Spreadsheet Intermediate Zoho Sheet

Learn Zoho Sheet ADDRESS syntax, absolute and relative address modes, R1C1 output, sheet-qualified text, examples, and troubleshooting.

Verification status: See the evidence and test scope described in this guide. How formula examples are checked

Quick answer: ADDRESS turns a numeric row and column into a text representation of a cell reference. For example, =ADDRESS(2;3) represents cell C2 as $C$2 in the default absolute A1 style. ADDRESS does not by itself retrieve the value stored in C2. That distinction matters when building navigation tools, calculated references, or formulas that eventually pass the generated text to INDIRECT. [Z1]

Evidence status (13 October 2026): Zoho’s English-language online Help specifies this function, its five arguments, the four address modes, and A1/R1C1 selection. Examples marked official below reproduce vendor-documented results; others are independently constructed reference calculations. No formula was executed in a signed-in Zoho Sheet workbook. No minimum release, paid plan, browser/desktop equivalence, or import-format equivalence was verified.

Availability and version history

  • Introduced: The original introduction version and date are not established by the checked sources.
  • Availability: Currently documented in Zoho Sheet’s rolling web-service help; no numbered release or retirement endpoint is established.
  • Scope: English-language web-service documentation. Mobile and offline-client parity is outside the verified scope.
  • Checked: October 14, 2026, against the official ADDRESS reference. Documentation check; not a new native test.

Syntax — including optional arguments

=ADDRESS(row; column; [mode]; [ref_type]; [sheet])
Argument Required? Input and interpretation
row Yes Number identifying the desired worksheet row; row 1 is the first row.
column Yes Numeric column index, beginning with 1 for A, 2 for B, 3 for C, and so on. Do not enter a bare letter as the column argument.
[mode] No Integer mode 1 through 4 selecting which of the row and column references are absolute. Default: 1.
[ref_type] No Logical TRUE to generate A1-style references or FALSE for R1C1-style output. Default: TRUE.
[sheet] No Text naming a sheet to include in the returned reference string. Quote the literal string, for example "ADDRESS".

Zoho displays semicolons in its reference syntax. Zoho also explains that when skipping an optional argument but supplying a later one, retain a delimiter for the empty position. For example, =ADDRESS(1;2;;TRUE;"ADDRESS") leaves mode empty and still supplies ref_type and sheet. Do not silently drop an interior delimiter: that changes which argument receives the value. The Help remark also mentions commas as separators, but their accepted use may depend on entry context and settings; this guide follows the exact semicolon examples published by Zoho. [Z1]

The four mode values

Mode Column part Row part Illustration for row 1, column 1
1 (default) Absolute Absolute $A$1
2 Relative Absolute A$1
3 Absolute Relative $A1
4 Relative Relative A1

Absolute versus relative describes the generated string’s dollar-sign markers, not an immediate change to the current cell. A result such as $C$2 still represents C2; the markers become important when the reference text is used in a context that interprets or adjusts references. Do not confuse choosing mode=4 with calculating an offset from the formula’s location. Zoho’s numeric row and column arguments identify the requested coordinates directly. [Z1]

Create a reproducible practice sheet

Start a blank Zoho Sheet sheet. Enter this table into A1:D4 (do not count formulas in G:H as part of this table). For consistent calculations, enter the decimal in C2 as numeric 1.5, C3 as 4, and C4 as 0.75; local number formatting may display the same values differently.

Row A: Product B: Quantity C: Unit price D: Line total
1 Product Quantity Unit price Line total
2 Pens 12 1.5 =B2*C2 → 18
3 Notebooks 6 4 =B3*C3 → 24
4 Clips 20 0.75 =B4*C4 → 15

Put the reference-making formulas in unused cells such as G2:G12. Because ADDRESS returns text, set those result cells to an ordinary text/general display rather than expecting price formatting. When checking the output, compare the dollar signs and any sheet qualifier exactly; omitting one is a meaningful change in the returned address.

Worked examples with expected results

Formula (enter in G2 or another unused cell) Expected output Evidence
=ADDRESS(1;2;1;TRUE;"ADDRESS") ADDRESS.$B$1 Official Zoho example [Z1]
=ADDRESS(1;2;1;TRUE) $B$1 Official Zoho example [Z1]
=ADDRESS(1;2;2) B$1 Official Zoho example [Z1]
=ADDRESS(1;2;1;FALSE;"ADDRESS") ADDRESS.R1C2 Official Zoho example [Z1]
=ADDRESS(2;3) $C$2 Independently constructed from documented defaults
=ADDRESS(3;4;4) D3 Independently constructed from documented mode 4
=ADDRESS(4;2;3) $B4 Independently constructed from documented mode 3
=ADDRESS(2;3;2) C$2 Independently constructed from documented mode 2
=ADDRESS(2;3;4;TRUE) C2 Independently constructed from documented mode 4
=ADDRESS(2;3;1;FALSE) R2C3 Independently constructed from documented R1C1 selection

Read each output as text. For instance, the $C$2 result tells you where the unit price for Pens is stored; it does not equal the number 1.5 and cannot be summed like a number. If your interface quotes returned text, the quotation marks may be a display convention; the reference itself is $C$2.

Test the optional-argument positions

The following three formulas illustrate why a blank interior argument is not interchangeable with dropping that position:

=ADDRESS(1;2)
=ADDRESS(1;2;;TRUE)
=ADDRESS(1;2;1;TRUE)

All three express the default absolute A1-style address for B1 and are independently expected to return $B$1. The second form intentionally leaves a blank slot between two semicolons; Zoho explicitly documents this style when skipping optional parameters. A formula such as =ADDRESS(1;2;FALSE) instead places FALSE in the numeric mode slot, not the A1/R1C1 slot; do not use it to request R1C1 output. [Z1]

Advanced use: create a reference and then evaluate it

To build a string for a cell whose row and column are stored in other cells, enter F2 = 2 and F3 = 3, and then put in G14:

=ADDRESS(F2;F3)

The independently calculated reference text is $C$2. The formula continues to point to the numeric coordinates in F2/F3: if F2 changes to 4 and F3 to 2, the expected text becomes $B$4. This is a simple way to preview a potential target without reading it.

Zoho separately documents the INDIRECT function as taking a string that represents a reference and evaluating it to return that referenced cell’s value. To retrieve the actual C2 value from the practice table, use this cross-function, independently reasoned example in a vacant cell:

=INDIRECT(ADDRESS(2;3))

Expected value: 1.5, because C2 holds the numeric price of Pens. This follows the documented roles of ADDRESS [Z1] and INDIRECT [Z2]; it is not a claim that we executed the composed formula in Zoho. Try the standalone ADDRESS result first. If the composition behaves differently in your sheet, check the referenced sheet, the generated text, and the INDIRECT reference-style argument. For R1C1-style text, Zoho documents INDIRECT(text_ref; FALSE); this should be used only with matching R1C1 address text. [Z2]

Why not always use INDIRECT? A direct reference such as =C2 is clearer when the target is fixed. A string-based lookup adds more opportunities for invalid text and is harder for another reader to audit. Prefer INDEX, when appropriate, for position-based retrieval from an existing range rather than constructing an address unnecessarily. Zoho separately documents INDEX and MATCH, including positional lookup behavior, but their exact argument conventions should be checked before combining them. [Z3][Z4]

Sheet-qualified references are Zoho-specific

Zoho’s examples of ADDRESS with a sheet argument use the syntax ADDRESS.$B$1 and ADDRESS.R1C2, with a period after the quoted sheet name’s text value has been applied. That is the exact documented output for the named sheet ADDRESS. Do not replace it in this Zoho guide with an exclamation mark merely because another spreadsheet displays a sheet qualifier differently. [Z1]

The Help article does not fully define escaping, quoting or error behavior for sheet names with spaces, apostrophes, or nonexistent names. Such cases should be manually checked in a real Zoho account before adding screenshots or promising portability. If a sheet argument is unnecessary, omit it. To create a human-readable audit label, the generated string alone is often sufficient.

Troubleshooting and documented limits

Symptom or question Diagnose it in this order
You see $C$2 but wanted 1.5. ADDRESS returns a reference string. Use a direct =C2 reference, or test INDIRECT separately if you need indirect evaluation. [Z1][Z2]
You see C$2 or $C2 unexpectedly. Inspect mode: 2 fixes row only, 3 fixes column only, 1 fixes both, 4 fixes neither. [Z1]
A formula shows R1C1 text such as R2C3. Verify the fourth argument: FALSE selects R1C1; the default TRUE selects A1. [Z1]
A generated sheet prefix looks unlike Excel. Retain Zoho’s documented qualifier rather than rewriting ADDRESS.$B$1 as an Excel-style reference. [Z1]
An optional parameter seems to shift one slot. Check all semicolons, especially an intentionally blank third argument before the fourth. [Z1]
#NAME! appears. Correct ADDRESS spelling, defined-name typos and unquoted literal sheet names. These are examples in Zoho’s general function error list. [Z1]
#VALUE! appears. Check that row and column are numeric and that mode is supplied as an appropriate numeric setting. Zoho identifies invalid argument types as a general source of this error; exact treatment of every invalid mode is not documented. [Z1]
#REF! appears after editing cells/sheets. Check that the referenced address or downstream INDIRECT reference is valid. Zoho associates this error with invalid cell references. Do not infer which edge-case input triggers it without native testing. [Z1][Z2]
Output differs after Excel/CSV import. Check whether the imported formula uses the expected delimiter, reference style and sheet naming. Import equivalence has not been demonstrated.

Unverified boundaries: Zoho Help does not specify the exact acceptable maximum row/column indexes, fractional/negative coercion behavior, the allowed domain of invalid mode values, or text quoting for all sheet names. These are not promises of support or precise error codes. Ordinary worksheet limits and account capabilities can change. The independent calculations cover only valid coordinate-to-string examples, not unsupported inputs.

Compatibility, environment and scope

  • Platform: Zoho’s English-language, cloud-oriented Sheet Help documents ADDRESS. This confirms public function documentation, not a guaranteed behavior in every account, mobile app, legacy product build or downloaded file format. [Z1]
  • Syntax: The vendor uses semicolon delimiters, and notes delimiter placeholders when optional arguments are omitted. Do not import another application’s formula punctuation as a substitute for this official syntax. [Z1]
  • Reference modes: 1–4 and A1/R1C1 selection are Zoho-documented. The period-delimited sheet-qualified results in the example table are also Zoho-documented. [Z1]
  • Locale: Numeric display and formula entry can vary with spreadsheet settings. This guide uses Zoho’s English reference examples with explicitly numeric columns and unformatted sample values; it does not assert a particular locale’s separator behavior.
  • Other spreadsheets: Even if a similarly named ADDRESS function exists elsewhere, name similarity does not prove compatible argument processing or sheet-qualified output. Cross-application exports should be tested in their target environment.
  • Test level: The independent calculations covered valid coordinate mapping and documented examples. No native Zoho execution, account eligibility test, edition/version matrix, or screenshot verification has occurred.

Official references and provenance (checked 2026-10-13)