If you look at many of Excel lookup functions you will find out that they either return a value or a position of an item. There is no function that returns the address where a lookup value is found.
Below is a code for a user defined function that takes two arguments: a lookup value and a range, and returns the address where the lookup value is found within the range. If the lookup value is not found, it will return the text “#N/A”.
You can download the function as a text from the Download Page.
If you need more help on how to create a user defined function click here.
Function LookupAddress(LookupVal As String, TheRange As Range)
Dim Cell As Range, Found As Boolean
For Each Cell In TheRange.Cells
If Cell.Value = LookupVal Then
LookupAddress = Cell.Address
Found = True
If Not Found Then LookupAddress = “#N/A”
Applies to Excel 2003