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 the cell address of the match in Excel.
How to Return Cell Address of Match in Excel: 3 Easy Ways
In this article, we will discuss how to return the cell address of the 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 of row 3 and column 1. These numbers are rows and columns 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
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 it is in column 1. The MATCH function returns row number: 3. So, the final is $D$8.
Read More: How to Use Cell Address in Excel Formula
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.
Read More: Excel VBA to Find Cell Address Based on Value
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 return the cell address instead of the value in Excel.
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.
Download Practice Workbook
You can download the practice workbook here.
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
<< Go Back to Excel ADDRESS Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
