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

While applying 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 cell address instead of value in Excel.


Download Practice Workbook

You can download the practice book from the link below.


5 Ways to Return Cell Address Instead of Value in Excel

Let’s say we have a dataset of some information about some employees of different companies such as their names, email, IP address, etc.

Dataset to Return Cell Address Instead of Cell Value in Excel

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 cell address instead of 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 concerning 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. And 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 exact match
  • 4 = column number (email address)

ADDRESS & MATCH function to Return Cell Address from Cell Value

  • 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

ADDRESS(MATCH(“Ambrosi”,B5:B14,0),4) = ADDRESS(8,4)

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.

Read More: How to Return Cell Address of Match in Excel (3 Easy Ways)


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 exact match
  • 4 = column number (email address)
  • address = info_type (the characteristic of the CELL function to return)

CELL,INDEX,MATCH functions to return Cell Address in Excel

  • Then, press ENTER to let the cell show the cell address instead of the cell value for the corresponding referred value.

Return Cell Address Instead of Cell Value in Excel

 💡 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 row and column of the corresponding dataset, not the Excel built-in dataset.

Output=>86.105.233.30

Finally, CELL(“address”,INDEX($B$5:$F$14,MATCH(“Ambrosi”,$B$5:$B$14,0),4)) = CELL(“address”,INDEX($B$5:$F$14,8,4)) = CELL(“address”,86.105.233.30) returns the address of 86.105.233.30.

So, final Output=> $E$12

Read More: Example of Cell Address in Excel (5 Ideal Cases)


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

Return Row Address in excel

  • 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

Return Column Address in Excel

Read More: How to Reference Cell by Row and Column Number in Excel (4 Methods)


4. Return 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, COLUMN functions. Let’s say we have got a similar named range in our dataset named “Info“.

Named Range in Excel

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))

Return Cell Address of a Named Range in Excel

  • Now, press ENTER, and all the cell addresses of the named range will appear.

Note: This method is only for 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 value from a range of data, don’t worry. Excel allows a user to find out the highest and the lowest value from a range and also the cell address of the highest and the lowest value. For this purpose, we will have to apply the MAX, MIN, ADDRESS, MATCH, and COLUMN functions.

Now, proceed like 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

Return Cell Address of 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

Return Cell Address of Lowest Value in Excel


Conclusion

In this article, I have tried to show you some methods to return cell address instead of 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. For more queries, kindly visit our website ExcelDemy. Have a great day!


Related Articles

Rafi

Rafi

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 worktime and made it easy for us to 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

Leave a reply

ExcelDemy
Logo