Function to return the address of a lookup value
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
Exit For
End If
Next Cell
If Not Found Then LookupAddress = “#N/A”
End Function
Applies to Excel 2003
