I'm developing a template program to allocate income amounts to 4 different
classes based upon prorate factors that can vary by category of income and
effective date of the prorate factors. The table of factors is on the same
sheet as the list (database) of income amounts to be allocated.

Here is a sample of prorate factors:
Category Date HSP CP
EpicInc 8/31/2001 0.49 0.51
InterestInc 8/31/2001 1
InterestInc 10/1/2002 0.6 0.4
RentInc 8/31/2001 1
RentInc 9/30/2002 0.56 0.50
RentInc 1/31/2004 1

Here is the Function proceedu

Function DepLookup(Cat As Range, TDate As Range)
' Looks up allocation based upon Category and date. IncTable is a named range
' on the worksheet containing the categories, effective dates and prorate
percentages.

Dim rng As Range
Dim workrange As Range, CatRows As Integer

Sheets("Database").Select
Set rng = Sheets("database").Range("IncTable")
rng.AutoFilter
rng.AutoFilter Field:=1, Criteria1:=Cat


'the following statement removes the first or header row
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Set rng = rng.SpecialCells(xlVisible)


If rng.Rows.Count = 0 Then
DepLookup = 0
End If
If rng.Rows.Count = 1 Then
DepLookup = rng.Range("C1").Value
End If
If rng.Rows.Count 1 Then
CatRows = rng.Rows.Count
Set workrange = rng.Range(Cells(1, 2), Cells(CatRows, 3))
DepLookup = Application.WorksheetFunction.Lookup(TDate, workrange)
End If

End Function

It works for the RentInc line item, but not the other categories. The
troubling thing is the effective date of a line of prorate factors. Prorate
factors are effective on and after the effective date and are superceded by a
line item with a later effective date. The data has dates back through
several years.

A sub proceedure with this same code works. I want a function procedure so
the results times the amount to be allocated will be entered in the cell with
the Function Proceedure.

Much of the code I borrowed from other entries/answers on this forum and I
appreciate the help a bunch. I've not found another that addresses this
problem specifically.

Any help will be appreciated.