ADDRESS function

ADDRESS returns a text string that represents a cell reference based on supplied row, column and optional absolute/relative settings.

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Generate a ADDRESS formula

Describe what you need. The generator will reach for ADDRESS where ADDRESS is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How ADDRESS reads its arguments
row_numrequiredcolumn_numrequiredabs_numoptionala1optionalsheet_textoptionalADDRESS →
ArgumentRequiredDescription
row_numRequiredNumber – the row index (1-based); values less than 1 or non-numeric produce #VALUE!.
column_numRequiredNumber – the column index (1-based); values less than 1 or non-numeric produce #VALUE!.
abs_numOptionalOptional number 1-4 controlling absolute/relative style; omitted defaults to 1 (both absolute), any other value yields #VALUE!.
a1OptionalOptional logical; TRUE (default) returns A1 style, FALSE returns R1C1 style; non-logical inputs cause #VALUE!.
sheet_textOptionalOptional text – the sheet name to prepend; if omitted the address is not qualified with a sheet.

Returns

A single text string representing the cell address.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Get absolute address of Food actual expense

CategoryMonthBudgetedActualVariance
HousingJan1200115050
FoodJan400420-20
TransportationJan150160-10
=ADDRESS(3,4)

Result: $D$3

Row 3 is the row where the Food category lives, and column 4 is the "Actual" column. With the default abs_num = 1, both row and column are absolute, so ADDRESS returns "$D$3".

2. Create a mixed reference for Transportation budgeted amount

CategoryMonthBudgetedActualVariance
HousingJan1200115050
FoodJan400420-20
TransportationJan150160-10
EntertainmentJan200250-50
=ADDRESS(4,3,2)

Result: C$4

Row 4 holds the Transportation line, column 3 is "Budgeted". The abs_num = 2 forces the row to be absolute while leaving the column relative, yielding the mixed reference "C$4".

3. Return an R1C1 address for Entertainment variance

CategoryMonthBudgetedActualVariance
HousingJan1200115050
FoodJan400420-20
TransportationJan150160-10
EntertainmentJan200250-50
=ADDRESS(5,5,1,FALSE)

Result: R5C5

The variance for Entertainment sits in row 5, column 5. By setting the a1 argument to FALSE, ADDRESS switches to R1C1 notation, so the result is "R5C5".

Common errors

Which ADDRESS error are you seeing?
ADDRESS returned an error#VALUE!
Provide a positive integer for both row_num and column_num.
#REF!
Use a row number within the worksheet’s limits (1-1048576 in Excel).
#VALUE!
Change abs_num to 1, 2, 3, or 4, or omit it to default to 1.
ErrorWhy it happensHow to fix it
#VALUE!row_num or column_num is less than 1 or not a number (e.g., =ADDRESS(0,2)).Provide a positive integer for both row_num and column_num.
#REF!row_num exceeds the sheet’s maximum rows (e.g., =ADDRESS(2000000,1) in Excel).Use a row number within the worksheet’s limits (1-1048576 in Excel).
#VALUE!abs_num is outside the allowed range 1-4 (e.g., =ADDRESS(2,3,5)).Change abs_num to 1, 2, 3, or 4, or omit it to default to 1.

Tips and when to use something else

  • Combine ADDRESS with INDIRECT to turn the generated text into a live reference.
  • Use ROW() and COLUMN() inside ADDRESS for dynamic addresses that move with your data.
  • When you only need the value at a computed location, XLOOKUP or INDEX is usually simpler than ADDRESS+INDIRECT.
  • Remember that abs_num = 4 gives a fully relative reference (e.g., "A1"), useful for constructing formulas that will be copied.

Related functions

INDIRECTINDIRECT returns the value or reference specified by a text string, letting you build dynamic cell or range references on the fly.ROWROW returns the row number of a given cell or range, or the row of the formula itself when no reference is supplied.COLUMNCOLUMN returns the column number of a given reference, or the column of the cell containing the formula when no reference is supplied.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.

Frequently asked questions

How do I make ADDRESS return a sheet-qualified reference?
Pass the sheet name as the fifth argument, e.g., =ADDRESS(2,3,1,TRUE,"Budget") returns "Budget!$C$2". The sheet name must be quoted if it contains spaces.
Can ADDRESS be used to reference a whole range?
ADDRESS returns a single cell address, not a range. To build a range string you can concatenate two ADDRESS calls, like =ADDRESS(2,2)&":"&ADDRESS(5,4).
Why does ADDRESS sometimes give me $A$1 instead of A1?
The default abs_num is 1, which makes both row and column absolute. Change abs_num to 4 for a fully relative address, or to 2/3 for mixed references.
Is ADDRESS available in Google Sheets?
Yes, Google Sheets supports ADDRESS with the same arguments and behavior as Excel, so the examples above work unchanged in both platforms.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator