PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

EXCEL FUNCTIONS-ADDRESS

INTRODUCTION

ADDRESS function comes under the LOOKUP AND REFERENCE category in Excel.

ADDRESS FUNCTION returns the address of any cell which is specified by the ROW number and COLUMN number.

It gives us the option of simple calculation of Row number and Column number as it is cumbersome to handle the column names after Z.

This function also gives additional option of setting which type of ADDRESS we want. A1 type or R1C1 type. A1 type is the standard system of naming where row is shown using a number and column is marked using an Alphabet.

Whereas in R1C1 method , both row and column are shown using numbers.

In this article, we’ll learn to use the ADDRESS FUNCTION, its purpose, formula syntax and various examples for the clarification purpose.

PURPOSE OF ADDRESS FUNCTION IN EXCEL

ADDRESS FUNCTION returns the cell address in a specified format when we pass the row and column number as arguments.

PREREQUISITES TO LEARN ADDRESS

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

Basic understanding of how to use a formula or function.
Basic understanding of rows and columns in Excel.
Some information about the financial terms is an advantage for the use of such formulas.
Of course, Excel software.

Helpful links for the prerequisites mentioned above

What Excel does? How to use formula in Excel?

SYNTAX: ADDRESS FUNCTION

The Syntax for the function is

=ADDRESS(ROW NUMBER, COLUMN NUMBER, ADDRESS RETURN TYPE, ADDRESS RETURN STYLE, SHEET NAME )

ROW NUMBER The row number to be used in the cell address

COLUMN NUMBER The column number to be used in the cell address.

ADDRESS RETURN TYPE The address can be returned as absolute , relative or mixed. The numbers and their application is shown in the table below.

ABSOLUTE RETURN TYPE	EFFECT ON THE RETURN TYPE
1 or omitted	Absolute [ e.g. $A$8]
2	Absolute row; relative column [ e.g. A$8]
3	Relative row; absolute column [ e.g. $A8]
4	Relative [ e.g. A8]

ADDRESS RETURN STYLE We can use the address in two styles in Excel sheets. The style are A1 style and R1C1 style.

A1 is the by default style where first letter shows the COLUMN NAME and second digit shows the ROW NUMBER.

R1C1 style treats both the values as numbers. i.e. the first digit shows the row number whereas the second digit shows the column number.

TRUE or OMITTED means the default style i.e. A1 style whereas

FALSE means the R1C1 style.

SHEET NAME is the name of the sheet we want to include with the address return. It might be need if we want to get the cell address of some other sheet.

The usage will be clarified when we solve the examples given below.

EXAMPLE:ADDRESS FUNCTION IN EXCEL

DATA SAMPLE

Let us take a few examples and see how ADDRESS function is working in the given situations.

	ROW NUMBER	COLUMN NUMBER	RETURN TYPE	RETURN STYLE	SHEET NAME

EXAMPLE 1	2	4	1	TRUE	SHEET

EXAMPLE 2	5	6	2	TRUE	[BOOK1]!SHEET1

EXAMPLE 3	3	4	3	FALSE

EXAMPLE 4	5	6	4	OMITTED

EXAMPLE 5	2	4	OMITTED	OMITTED	OMITTED

EXAMPLE 6	1	4	2	1

STEPS TO USE ADDRESS FUNCTION

FOLLOW THE STEPS TO USE ADDRESS FUNCTION IN EXCEL

We have taken various examples to show the use of ADDRESS FUNCTION.

Select the cell where we want to obtain the cell address.

Enter the function as shown in the picture above.

The result will appear in the cell.

NOTE: In the examples, we have used the cells references containing the data. We can use the data directly too.

We can always use 1 in place of TRUE and 0 in place of false. The results will be accurate.

EXPLANATION

We have taken various examples to show the use of ADDRESS FUNCTION.

EXAMPLE 1

The formula used is =ADDRESS(D8,E8,F8,G8,H8)

Here we have used all the five arguments. It has returned an absolute address with the sheet name.

EXAMPLE 2

The formula used is =ADDRESS(D10,E10,F10,G10,H10).

In this example, we have used all the five arguments. This example returned relative column and absolute row, with the workbook and sheet name.

We can see that FIFTH ARGUMENT is simply attached with the address. So always be careful to give it in proper format so that our address is working properly.

EXAMPLE 3

The formula used is =ADDRESS(D12,E12,F12,G12,H12)

We have used all the five arguments in this example but haven’t given any sheet name. The style has been taken as false so that we get the result in R1C1 style. The result is as per expectation.

EXAMPLE 4

The formula used is =ADDRESS(D14,E14,F14)

In this example, we used only the first three arguments. The result is simple relative address as per the given arguments.

EXAMPLE 5

The formula used is =ADDRESS(D16,E16)

We have used only the two required arguments and the result is simple absolute address.

EXAMPLE 6

The formula used is =ADDRESS(D18,E18,F8,G18,H18)

NOTE: DID YOU NOTICE AN EXCLAMATION BEFORE THE ADDRESS IN THE EXAMPLES WITH EMPTY ARGUMENT. IT WAS SO BECAUSE WE TOOK AN EMPTY SHEET NAME. IF WE DON’T WANT SHEET NAME, WE SHOULD OMIT IT.

CONTENTS