Download FREE Address Function Excel Template and Follow Along!
Address Function Excel Template.xlsx

Table Of Contents

arrow

Address Function in Excel

Publication Date :

Blog Author :

Edited by :

Syntax of the ADDRESS Function

The syntax is stated as follows:

Address Function Formula

The function accepts the following mandatory arguments:

  • Row_Num: This is the row number used in the cell reference. The “row_num=1” represents row 1.
  • Column_Num: This is the column number used in the cell reference. The “column_num= 2” represents column B.

The function accepts the following optional arguments:

  • Abs_num (absolute number): This is the type of cell reference–absolute or relative. If this parameter is omitted, the default value is set at 1 (absolute). It can have any of the following values depending on the requirement:
Abs_numExplanation
1Absolute reference like “$A$1”
2Relative column, absolute row like “A$1”
3Absolute column, relative row like “$A1”
4Relative reference like “A1”
  • A1: This is the reference style used. It can be either “A1” (1 or true) or “R1C1” (0 or false). If this parameter is omitted, the default style is “A1.”
  • Sheet_text: This is the name of the sheet used in the cell address. If this parameter is omitted, no worksheet name is used.

How to Use the Address Function in Excel? (With Example)

Let us consider the various possible outcomes of the ADDRESS function, as shown in the succeeding image.

The observations of a few rows are explained as follows:

1. The Third Row

  • The “row=1” and “column=4.”
  • The formula is “=ADDRESS(1,4).”
  • By default, the parameters “absolute number” and “reference type” are set at 1.
  • The result is an absolute address with an absolute row and an absolute column, i.e., “$D$1.”
  • The “$D” signifies the absolute column “4” and “$1” signifies the absolute row “1.”
Address Function Example

2. The Fifth Row

  • The “row=5,” “column=20,” and “abs_num=2.”
  • The ADDRESS formula can be rewritten in a simpler form as “=ADDRESS(5,20,2).”
  • The reference style is set at 1 (true) when it has not been defined explicitly.
  • The result is “T$5,” which has an absolute row ($5) and a relative column (T).
Example 1

3. The Seventh Row

  • We pass all the arguments of the ADDRESS function, including the optional ones.
  • The “row=10,” “column=9,” “abs_num=4,” “A1=1,” and “sheet_name=Example1.”
  • The address formula can be rewritten in a simpler form as “=ADDRESS(10,9,4,1,"Example1").”
  • The result is “Example1!I10.”
  • Since the “absolute number” parameter is set at 4, the result is a relative reference (“I10”).
Example 2

How to Use the INDIRECT Function to Pass the Address?

The reference of a cell can be derived with the help of the ADDRESS function. However, if we are interested in obtaining the value stored in the Excel address of cells, we use the INDIRECT function. With this function, we can get the actual value through reference.

Syntax of the INDIRECT Function

Address function (Indirect function formula)

The function accepts the following arguments:

  • Ref_text: This is the cell reference supplied in the form of a text string.
  • A1: This is a logical value that specifies the style of reference. It can be either “A1” (true or omitted) or “R1C1” (false).

The first argument is mandatory, while the second is optional.

Let us consider an example.

The data is given in the following image. We want to find the value of the cell reference passed in the INDIRECT function.

In the first observation, the “ADDRESS=$D$3.” We rewrite the function as “=INDIRECT(A3).”

In cell D3, under the sample data, the value is “Mathematics.” This is the same as the result of the INDIRECT function in cell B3.

Address Function Example 3

Let us consider another example in which the reference style is R7C5.

Here, we must set the “ref_type” to “false” (0), so that the INDIRECT function can read the reference style.

The output of “=INDIRECT(A8,0)” is 87%.

Example 4

Frequently Asked Questions (FAQs)

1

What are the uses of the ADDRESS function of Excel?

Arrow down filled
2

How does the ADDRESS function return the address of the cell with the highest value?

Arrow down filled
3

How does the ADDRESS function return the complete address of a named range?

Arrow down filled