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:

Initial data set

 

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:

Final data set

 

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.

 Get the Cell Address Instead of Value in Excel

 

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

Final result

 

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.

 Get the Cell Address Instead of Value in Excel

 

4. And tada! We have successfully used the CELL function to get the cell address.

 Get the Cell Address Instead of Value in Excel

 

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.

Get emails from us about Google Sheets.

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.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like