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

Get FREE Advanced Excel Exercises with Solutions!

We typically utilize the cell address or cell reference to return a cell value when performing an Excel formula. However, if you’re working with a large Excel sheet of data and need to find a cell having a certain piece of text in it, you’ll need to sift through a lot of information. But it is also a very time-consuming strategy and not very sensible. In this article, we will show how to return cell address of match in Excel.


Download Practice Workbook

You can download the practice workbook here.


3 Easy Ways to Return Cell Address of Match in Excel

In this article, we will discuss how to return cell address of match in Excel. Firstly, we will combine the CELL, INDEX, and MATCH functions. Then, we will use the ADDRESS and MATCH functions together. Finally, we will opt for a VBA code to solve the issue.


1. Using Combination of CELL, INDEX, and MATCH Functions

The CELL function provides details on a cell’s layout, location, or contents. The INDEX function extracts a value from a table or range, or a reference to a value, and returns it. The MATCH function looks for a specific item in a range of cells and returns the item’s location in the range relative to the other items.

Steps:

  • Firstly, select the E5 cell and write down the following formula in the cell,
=CELL("address",INDEX($B$5:$C$10,MATCH("Alisa",$B$5:$B$10,0),1))
  • Then, hit Enter.

inserting formula in excel to return cell address of match

  • Consequently, we will get the cell address of the desired cell.

combining cell, index, and match in excel to return cell address of match

🔎 Formula Break Down:

  • MATCH(“Alisa”,$B$5:$B$10,0): It looks for an exact match of the value “Alisa” in the lookup array B5:B10 and finds the value in row number 3.
  • INDEX($B$5:$C$10,MATCH(“Alisa”,$B$5:$B$10,0),1) : It returns the cell value at the intersection row 3 and column 1. These numbers are row and column of the corresponding dataset. The output is “Alisa”.
  • CELL(“address”,INDEX($B$5:$C$10,MATCH(“Alisa”,$B$5:$B$10,0),1)): It returns the address of “Alisa” and it is B7.

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


2. Applying ADDRESS and MATCH Functions in Combination

The ADDRESS function returns the cell address of a cell in the worksheet if we give the row and column number for the cell. Here, we will use it with the MATCH function to find out the cell address.

Steps:

  • Firstly, choose the E5 cell and write the following formula down in the cell,
=ADDRESS(MATCH("Alisa",B5:B10,0),2)
  • Then, hit the Enter button.

typing formula in excel to return cell address of match

  • Consequently, we will get the cell address of the cell containing the particular value.

combining address and match functions in excel to return cell address of match

🔎 Formula Break Down:

  • MATCH(“Alisa”, B5:B10,0): It looks for an exact match of the value “Alisa” in the lookup array B5:B10 and finds the value in row number 3.
  • ADDRESS(MATCH(“Alisa”, B5:B10,0),2): We want to get the cell address of “Alisa” and the it is in column 1. And the MATCH function returns row number: 3. So, final is $D$8.

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


3. Using VBA Code

In this final method, we will resort to a simple VBA code to find the cell address. Here, we will find out the cell address of a selected cell.

Steps:

  • Firstly, select any cell of which you want to find out the cell address.
  • Here, we will select the C7 cell.
  • Firstly, go to the Developer tab in the ribbon.
  • From there, select the Visual Basic tab.
  • Consequently, the Visual Basic window will be opened.

opening visual basic window in excel to return cell address of match

  • After that, in the Visual Basic tab, click on Insert.
  • Then, select the Module option.
  • Consequently, a coding module will appear.

  • In the coding module, write down the following code.
  • Then, save the code.

writing code in excel to return cell address of match

Sub cell_address()
Dim cell_address As String
cell_address = ActiveCell.Address
Cells(5, 5).Value = cell_address
End Sub
  • Finally, go to the Run tab and click on it.
  • From the drop-down option, select the Run command to run the code.

  • Consequently, we will get the cell address.


How to Return Cell Address Instead of Value in Excel

In this method, we will use the MATCH function to return only the row and column number of a particular cell. In the process, we will not return the value of the cell.

Steps:

  • Firstly, select the E5 cell and write down the following formula in the cell,
=MATCH("Alisa",$B$5:$B$10,0)
  • Then, hit Enter.

  • Consequently, we will get the row number of the cell that contains the value.

  • Again, choose the E8 cell and type the following formula,
=MATCH("Alisa",$B$7:$C$7,0)
  • Then, hit Enter.

  • As a result, we will get the column number of the cell.

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


Conclusion

In this article, we have discussed 3 methods to return the cell address of a match to a particular value in excel. This will allow the users to find the required cell containing a particular value easily.


Related Articles

Adnan Masruf
Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo