This is the macro solution. My preference is to always use macro
instead of a complicated formula. Macros are easier to debug and can be
documented. Complicated formulas cannot be documented. Macros also
run more effiecently. The macro could be made into a UDF.

Sub AddEffectiveDate()

Set ActivitySht = Sheets("Sheet1")
Set EffectivitySht = Sheets("Sheet2")

With ActivitySht
.Range("D1") = "Effective Cost"
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
EffectiveDate = 0
ResID = .Range("A" & RowCount)
ActivityDate = .Range("C" & RowCount)

'search Effectivity Sheet
With EffectivitySht
Set c = .Columns("A").Find(what:=ResID, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
NewEffectiveDate = c.Offset(0, 2)
'only take costs before the activity dates
'and take latest date
If NewEffectiveDate <= ActivityDate And _
NewEffectiveDate EffectiveDate Then
NewCost = c.Offset(0, 1)
EffectiveDate = NewEffectiveDate

End If
Set c = .Columns("A").FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < FirstAddr
End If

End With

If EffectiveDate = 0 Then
.Range("D" & RowCount) = "Not Found"
Else
.Range("D" & RowCount) = NewCost
End If
Next RowCount

End With


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=190056

http://www.thecodecage.com/forumz