ADDRESS formula in Excel generates the address for a cell when provided a row and a column number.
ADDRESS Formula Syntax
ADDRESS Formula has five parts:
ADDRESS (row_num, col_num, ref_type, ref_style, sheet_name)
row_num is the row number in which the cell for which you are trying to generate the address is located. It is specified as a number.
col_num is the column number in which the cell for which you are trying to generate the address is located. It again is specified a number.
ref_type type of address you want to return. Bear in mind that there are three address types, namely :
Absolute (Ex. $A$1)
Relative (Ex. A1)
Mixed – Absolute Row and relative column (Ex. A$1) or Absolute column and relative row (Ex. $A1)
To get the absolute reference type of cell address using the ADDRESS formula, you can either specify this parameter as 1 or leave it blank (Ex. =ADDRESS(1,3,1) or simply =ADDRESS(1,3) ) The optional parameter is optional in the ADDRESS formula and when left blank the formula will generate the absolute type of reference.
ref_style is the reference style which signifies whether the address returned in formatted in the A1 style or the R1C1 style. This needs to be either TRUE or FALSE (FALSE can also be specified as 0 and TRUE by any number <> 0).
A1 Style Reference – Will show the cell address as [col][row] (Ex. A2, or $A$2)
R1C1 Style Reference – Will show the cell address as R[row_num]C[col_num] (Ex. RC where the numbers in the parenthesis indicate the row and the column numbers respectively.)
This parameter is optional and if omitted will return the A1 type of reference. You can specify this as =ADDRESS(1,2,4,TRUE) or =ADDRESS(1,2,TRUE) (omitting the third parameter) or =ADDRESS(1,2) (omitting the third and the fourth parameter).
sheet_name is the sheet name in which the cell is located. If nothing is specified, the sheet name is omitted from the address being returned by the formula. If the cell is located in any other worksheet than the present one, you can specify this as a string enclosed in quotes (=ADDRESS(1,2,4,TRUE,”Sheet2″) ). For this parameter to work correctly, you need to have both the ref_type and ref_style parameters specified correctly.
Example of a ADDRESS Formula
Let’s look at an example of the ADDRESS formula. Suppose we wanted to generate the address for a particular cell in which a value is stored. We could simply write =ADDRESS(1,2,4,TRUE,”Sheet1″). The result in this case will be Sheet1!B1 as shown in the example above. We could also have entered the formula as =ADDRESS(1,2,4,TRUE) or =ADDRESS(1,2,4) and even as =ADDRESS(1,2) and all would have returned the reference to the same cell. The last format is the one used commonly and omits any optional parameter. The result by using the last parameter would simply have been $B$1.
More often than not, the ADDRESS function is used as an intermediate step to generate a cell address and then use that as the input to the INDIRECT function. By using a combination of ADDRESS and INDIRECT functions, you can complete a full circle – starting from reading the address of the cell and then using that address to return the value stored at that address.
In the example above, we check for the accuracy of the returned address by using it as the input to in INDIRECT function. Simply write =INDIRECT(cell_where_you_entered_the_ADDRESS_Formula) and that should give you the initial value (which was the string “Mike” stored in B1 in the example)
How to enter the ADDRESS formula in an Excel Sheet
1. Select the cell in which you want to place the formula
2. Type the formula as =ADDRESS(
3. Then enter the number which corresponds to the row of the cell whose address you are trying to generate
4. Press the comma key (,)
5. Then enter the number which corresponds to the column of the cell whose address you are trying to generate.
6. At this point, you can omit all the remaining parameters and simply close the formula by entering the closing bracket ). However if you want, you can enter the remaining optional parameters. In that case, ignore the first part of step 6.)
7. Press the comma key (,)
8. Enter a value from (1,2,3,4) as the ref_type which indicates the type of address (relative or absolute) to return. (see above for description)
9. Press the comma key (,)
10. Enter a value as TRUE or FALSE indicating the style of address you would like to return (A1 style ot RC style. (see above for description)
11. Press the comma key (,)
12. Enter the sheet name in which the cell is located in brackets (Ex. “Sheet1″ or as
13. Close the formula bracket by typing in the closing bracket ).
Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =ADDRESS(1,2,4,TRUE,”Sheet1″)
Possible Errors with the ADDRESS Formula
The ADDRESS formula can result in the following error values:
ADDRESS Formula #VALUE! Error
The #VALUE! error can result from a wrong value of the row or column numbers being specified or by specifying a reference type outside of 1,2,3 or 4. For example if you entered the ADDRESS formulas a =ADDRESS(1,20000,4,TRUE,”Sheet1″), it would give you the #VALUE! error because the column number has been wrongly specified. A #VALUE! error typically signifies that there is a problem with the first three parameters of the formula. If you get this error, check the row and the column number and see if they can refer to cells which are actually located on the worksheet. Then check if you’ve erroneously specified a reference type value that is not in the admissible list.
ADDRESS #NAME? Error
The #NAME? Error value in ADDRESS formula can occur when you specify the wrong type of reference style value. The reference style value can be either TRUE or FALSE (FALSE can also be specified as 0 and TRUE by any number <> 0). This error can also occur when you specify the row or the column number as anything other than a numeric value and which does not equate to a numeric value. For example =ADDRESS(1,SUM(2,0),8,1,”Sheet1″) is a valid example of the ADDRESS function but =ADDRESS(1,test,8,1,”Sheet1″) is not.
You can download an example of ADDRESS formula here or click on the button below: