# How to Return Cell Address Instead of Value in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

While applying the Excel formula, we frequently use the cell address or cell reference to return a cell value. But if you work on an Excel sheet of a large amount of data and looking for a cell with specific text in it, you need to go through a large amount of data. But that’s not a smart approach and at the same time, it is very time-consuming. But don’t worry at all. In this article, I will show you how to return the cell address instead of the value in Excel.

## How to Return Cell Address Instead of Value in Excel: 5 Ways

Let’s say we have a dataset of some information about some employees of different companies such as their names, email, IP address, etc. From this dataset, we want to return the cell address from a specific cell value.

In this section, you will find 5 common ways to return the cell address instead of value in Excel. I will demonstrate them one by one here. Let’s check them now!

### 1. Using ADDRESS and Match Functions

Let’s say, we have randomly chosen a cell value “Ambrosi”. The concerning dataset got the email addresses of the persons. We want to know the email address of “Ambrosi” and for this, we need to know the cell address in which the corresponding email id is located.

In order to find out the cell address, we will create a formula by using the ADDRESS and MATCH functions.

The MATCH function looks for a value in a defined lookup array for either an exact match or greater/less than the lookup value. The ADDRESS function gives the row and column number.

Proceed with the following steps to figure it out.

📌 Steps:

• First of all, select a cell to find out the cell address and type the following formula into the cell.

`=ADDRESS(MATCH("Ambrosi",B5:B14,0),4)`

Here,

• Ambrosi = lookup value
• B5:B14 = lookup array
• 0 = for the exact match
• 4 = column number (email address) • Then, hit ENTER and you will get the cell address of the referenced cell value. 💡 Formula Breakdown

MATCH(“Ambrosi”,B5:B14,0) looks for an exact match of the value “Ambrosi” in the lookup array B5:B14 and finds the value in row number 8.

Output=> 8

We want to get the email address of Ambrosi and the email address is in column 4. And the MATCH function returns row number: 8

So, final Output=> \$D\$8

Note: While using the MATCH function, don’t use the whole range of data as the lookup array, it will result in #N/A, Just the column in which the referenced cell value is located. ### 2. Combining CELL, INDEX and MATCH Functions

For the same set of data and the same lookup value (Ambrosi), we now create a formula by combining CELL, INDEX, and MATCH functions.

The INDEX function returns the cell reference at the intersection of a specific row and a column, The CELL function gives specific information (address, color, content, filename, format, etc.) about a referred cell.

In order to demonstrate this formula, just follow the steps below.

📌 Steps:

• Firstly, type the following formula in a selected cell to get the address.

`=CELL("address",INDEX(\$B\$5:\$F\$14,MATCH("Ambrosi",\$B\$5:\$B\$14,0),4))`

Here,

• Ambrosi = lookup value
• B5:B14 = lookup array
• 0 = for the exact match
• 4 = column number (email address)
• address = info_type (the characteristic of the CELL function to return) • Then, press ENTER to let the cell show the cell address instead of the cell value for the corresponding referred value. 💡 Formula Breakdown

MATCH(“Ambrosi”,B5:B14,0) looks for an exact match of the value “Ambrosi” in the lookup array B5:B14 and finds the value in row number 8.

Output=> 8

INDEX(\$B\$5:\$F\$14,MATCH(“Ambrosi”,\$B\$5:\$B\$14,0),4) = INDEX(\$B\$5:\$F\$14,8,4) returns the cell value at the intersection row 8 and column 4. These numbers are rows and columns of the corresponding dataset, not the Excel built-in dataset.

Output=>86.105.233.30

So, final Output=> \$E\$12

### 3. Return Row and Column Number Instead of Value

If you want to find out the row and the column number of the concerned dataset but not the Excel built-in row and column, then you can follow this procedure.  For this, just proceed with the steps below.

📌 Steps:

• First of all, apply the following formula to get the row number.

`=MATCH("Ambrosi",\$B\$5:\$B\$14,0)`

Here,

• Ambrosi = lookup value
• \$B\$5:\$B\$14 = lookup array • Then, apply the following formula to get the column number.

`=MATCH("Ambrosi",\$B\$12:\$F\$12,0)`

Here,

• Ambrosi = lookup value
• \$B\$12:\$F\$12 = lookup array ### 4. Return the Cell Address of a Named Range

Sometimes your Excel worksheet may contain a named range for a range of data. If you want to return the cell address instead of the cell value, you need to apply the ADDRESS, ROW, and COLUMN functions. Let’s say we have got a similarly named range in our dataset named “Info“. We want to find out the cell address of this named range.

So, let’s start the process like the one below.

📌 Steps:

• First of all, select. a cell and type the following formula into the selected cell.

`=ADDRESS(ROW(Info),COLUMN(Info))` • Now, press ENTER, and all the cell addresses of the named range will appear. Note: This method is only for the Excel 365 version.

If you run other versions of Excel than 365, then you have to use the following individual formula to get the first cell of the range, the last cell of the range, and the whole range.

• First Cell of the Range:

`=ADDRESS(ROW(Info),COLUMN(Info))`

• Last Cell of the Range:

`=ADDRESS(ROW(Info)+ROW(Info)-1,COLUMN(Info)+COLUMN(Info)-1)`

• The Whole Range:

`=ADDRESS(ROW(Info),COLUMN(Info)&":"&ADDRESS(ROW(Info)+ROW(Info)-1,COLUMN(Info)+COLUMN(Info)-1)`

### 5. Return Cell Address of Highest and Lowest Value

If you want to find out the cell address of the highest and the lowest cell values from a range of data, don’t worry. Excel allows a user to find out the highest and the lowest values from a range and also the cell addresses of the highest and the lowest values. For this purpose, we will have to apply the MAX, MIN, ADDRESS, MATCH, and COLUMN functions.

Now, proceed in the steps below.

📌 Steps:

• First of all, apply the MAX function to get the highest value from a data range.

`=MAX(D5:D14)`

Here, D5:D14 = lookup range • Then, apply the MIN function to get the lowest value of the corresponding range.

`=MIN(D5:D14)`

Here,

• D5:D14 = lookup range • After that, apply the following formula to get the cell address of the highest value. The MATCH function gives the row number and the COLUMN function gives the column number. Finally, the ADDRESS function returns the cell address.

`=ADDRESS(MATCH(H5,D:D,0),COLUMN(D5:D14))`

Here,

• H5 = the highest value • Now, apply the following formula to get the cell address of the lowest value.

`=ADDRESS(MATCH(I5,D:D,0),COLUMN(D5:D14))`

Here,

• I5 = the lowest value

## Conclusion

In this article, I have tried to show you some methods to return the cell address instead of the value in Excel. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Rafiul Hasan

Hey there! I am Md. Rafiul Hasan. Currently, I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our work time and made it easy for us to perform quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  