Adnan Masruf
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.
- Consequently, we will get the cell address of the desired cell.
🔎 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.
- Consequently, we will get the cell address of the cell containing the particular value.
🔎 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.
- 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.
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.
