This guide will explain **how to get the cell address instead of the value in Excel** **using two easy methods**.

##### Table of Contents

The rules for using the `ADDRESS`

function in Excel are the following:

- The
`ADDRESS`

function can return a relative, mixed, or absolute reference. Furthermore, the function always returns the reference as a text value. - When the abs_num argument is left blank, the function will default to absolute.
- When the a1 argument is left blank, the function will default to an A1 style.
- When the sheet argument is left blank, the function will use the current sheet by default.

Excel is an excellent tool that makes organizing, analyzing, and listing data very simple and easy. For example, we can easily retrieve values from a data set using different functions. So we would only need to input the cell reference. Then, Excel will return the value inside the inputted cell.

However, there will be times when we need the cell address rather than the value inside the cell. But, worry not! Excel also has functions that perform this specific task of getting the cell address instead of the value.

For instance, we can use the `ADDRESS`

function to retrieve the cell address. And we can also utilize the `CELL`

function, which can perform similar things.

Let’s take a sample scenario wherein we need to get the cell address instead of the value in Excel.

Suppose you have a data set containing a list of values. Since you will be performing calculations later, you want to prepare the components by retrieving the cell addresses instead of the values inside the cell.

Rather than manually checking what the cell address of each value is in, you opted to use the `ADDRESS`

function to perform this task quickly and easily.

Great! Before we move on, let’s first learn how to write the `ADDRESS</code`

> function in Excel.

**The Anatomy of the ADDRESS Function**

The syntax or the way we write the `ADDRESS`

function is as follows:

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

Let’s take apart this formula and understand what each term means:

**=**the equal sign is how to begin any function in Excel.**ADDRESS()**refers to our`ADDRESS`

function. And this function will create a cell reference as a text based on the specific row and column numbers we input.**row_num**is a required argument. And this refers to the row number we want to use in the cell reference. For example, we input 1, which means row 1.**column_num**is another required argument. So this refers to the column number we want to use in the cell reference. If we input 3, we are referring to column C.**abs_num**is an optional argument. And this will specify the reference type, whether it will be an absolute reference or a relative reference.**a1**is another optional argument. So this is a logical value that will specify the reference style.**sheet_text**refers to a text that will specify the name of the worksheet to be used as the external reference. And this is also an optional argument.

Now let’s learn how to write the `CELL`

function in Excel.

**The Anatomy of the CELL Function**

The syntax or the way we write the `CELL`

function is as follows:

=CELL(info_type, [reference])

Let’s take apart this formula and understand what each term means:

**=**the equal sign is how to start any function in Excel.**CELL()**is our`CELL`

function. And this function will return information about the inputted cell. For instance, the address, formatting, and content of the selected cell.**info_type**is a required argument. And this refers to the specific type of information we are looking for. For example, we want to return the address. So we will input “address”.**reference**is an optional argument. So this will specify which cell address we are locating.

Awesome! Now let’s dive into a real example of how to get the cell address instead of the value in Excel.

**A Real Example of Getting the Cell Address Instead of Value in Excel**

Let’s say we have a data set containing the monthly sales of each fruit product. And we want to locate the cell address of the monthly sales rather than getting the value of the monthly sales. So our initial data set would look like this:

Firstly, we can simply use the `ADDRESS`

function to get the cell address of the monthly sales. So we can create a dropdown list of the product to help us get the cell address of its monthly sales. But, we will be combining the `ADDRESS`

function with the `MATCH`

function to look for and retrieve the cell address.

So the `MATCH`

function will find the cell’s row number containing the lookup value, which in this case is the fruit product. Then, we can input the functions and retrieve the cell address of the monthly sales of the inputted fruit product.

Secondly, we can also utilize the `CELL`

function to retrieve the cell address. So this will follow the same process. But, this time, we will combine the `CELL`

function with the `MATCH`

function and `INDEX`

function to get the cell address.

So the `INDEX`

function will return the value based on the row and column numbers we input. At the same time, it will return the reference of the cell containing that specific value.

And our final data set would look like this:

You can make your own copy of the spreadsheet above using the link attached below.

Finally, let’s learn the process of how to get the cell address instead of the value in Excel.

**How to Get the Cell Address Instead of Value in Excel**

In this section, we will discuss how to use the `ADDRESS`

function and the `CELL`

function to get the cell address instead of the value in Excel.

1. Firstly, we will input our lookup value in another cell. In this case, we will get the cell address of the monthly sales of Banana. So our lookup value would be Banana. Then, type in the `ADDRESS`

function with the formula “**=ADDRESS(MATCH(F2,B1:B5,0),3)**”. Lastly, press the **Enter **key to return the cell address.

2. And tada! We successfully got the cell address instead of the value in Excel using the `ADDRESS`

function.

3. Secondly, let’s try using the `CELL`

function to retrieve the cell address. So we will be using the same lookup value. In this case, we will input the `CELL`

function with the formula “**=CELL(“address”, INDEX(B1:C5,MATCH(F2,B1:B5,0),3))**”. Then, press the **Enter **key to get the result.

4. And tada! We have successfully used the `CELL`

function to get the cell address.

And that’s pretty much it! We have discussed how to get the cell address instead of the value in Excel using two methods specifically the `ADDRESS`

function and the `CELL`

function. Now you can use any of the two methods to apply in your work whenever you need to.

Are you interested in learning more about what Excel can do? You can now use the `ADDRESS`

function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.