I don't know of a formula that will do this, BUT I do have a
User-Defined-Funtion that will do this...
'/==========================================/
Public Function vLookupAddress(Lookup_Value As Variant, _
Lookup_Range As Range, Col_Index_Num As Long) As String
'Lookup_Value = item to be looked up
'Lookup_Range = range that the lookup will search
'Col_Index_Num = the column to be searched
' ie: if the Lookup range is C5:G10 and you want the
' Address of the information in G, the Col_Index_Num
' would be 5 because C=1, D=2,...G=5
Dim iFirstRow As Long, iLastRow As Long
Dim rng As Range
Dim strFirstColumn As String, strRangeName_Full As String
Dim strRangeWorksheet As String, strRangeWorkbook As String
Dim varValue As Variant
Application.Volatile
On Error GoTo err_Function
vLookupAddress = "Not Found"
'get workbook and worksheet names that range is in
strRangeWorkbook = Lookup_Range.Parent.Parent.name
strRangeWorksheet = Lookup_Range.Parent.name
'get 1st and last rows in range
iFirstRow = Lookup_Range.Row
iLastRow = Lookup_Range.CurrentRegion.Rows.Count + _
Lookup_Range.CurrentRegion.Row - 1
'get 1st column in range
strFirstColumn = _
ColumnLetterFromNumber(Lookup_Range.Column)
Set rng = _
Application.Workbooks(strRangeWorkbook). _
Worksheets(strRangeWorksheet).Range(strFirstColumn & ":" & _
strFirstColumn)
'get the relative position of an item in an array that
' matches a specified value in a specified order
strRangeName_Full = "[" & strRangeWorkbook & "]'" & strRangeWorksheet & _
"'!" & Lookup_Range.Address
varValue = _
Application.Match(Lookup_Value, rng, False)
If IsError(varValue) Then
vLookupAddress = "Not Found"
GoTo exit_Function
End If
vLookupAddress = "[" & strRangeWorkbook & "]" & _
strRangeWorksheet & "!" & _
ColumnLetterFromNumber(rng.Column + Col_Index_Num - 1) & _
varValue
exit_Function:
Set rng = Nothing
Exit Function
err_Function:
vLookupAddress = "Not Found"
GoTo exit_Function
End Function
'/==========================================/
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown
AZSteve said:
"Workbook A/worksheet A" contains a reference cell with a date (formatted
date number). I need a formula for this worksheet that gives the cell
address in range B1:G65 of "workbook B/worksheet C" that matches the contents
of the reference cell. Some of the cells in B1:G65 of workbook B/worksheet C
contain date numbers and one will match. I will use that address to import
data from cells below the matching cell.
