How to Retrieve the Cell Address of a Match in Excel (3 Methods)

Method 1 – Using a Combination of CELL, INDEX, and MATCH Functions:

Steps:

=CELL("address",INDEX($B$5:$C$10,MATCH("Alisa",$B$5:$B$10,0),1))

The result will be the cell address of the desired value (Alisa), which is B7.

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

Steps:

=ADDRESS(MATCH("Alisa",B5:B10,0),2)

The result will be the cell address containing the specific value (Alisa), which is $B$3.

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): It looks for the cell address of “Alisa” and it is in column 1, row number 3 of the array. So, the result is $B$3.

Steps:

Sub cell_address()
Dim cell_address As String
cell_address = ActiveCell.Address
Cells(5, 5).Value = cell_address
End Sub

Steps:

=MATCH("Alisa",$B$5:$B$10,0)

=MATCH("Alisa",$B$7:$C$7,0)

Download Practice Workbook

You can download the practice workbook from here:

Return Cell Address of Match.xlsm