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

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

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:

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.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

## How to Rank Non-Continuous Range in Excel

This guide will explain how you can use the RANK function to rank a non-continuous range in Excel.…

## How to Calculate Variance in Excel

This guide will explain how to calculate variance in Excel using the different variance functions.  Excel has six…

## How to Create a Win-Loss Sparkline Chart in Excel

This guide will explain how to create a Win-Loss sparkline chart in Excel to visualize positive and negative…

## How to Create Entity Relationship Diagram in Excel

This guide will discuss how to create an entity relationship diagram in Excel using the Insert Shapes feature.…