Multi-lookups on variable data
 

I'm trying to design a formula that will calculate interest on a daily basis
for a loan which has a floating interest rate based on a Bank's Prime Lending
Rate.

I have a separate worksheet which contains a table with Lending Rate
Effective Date (A!A2:A2000), End Date (A!B2:B2000), Lending Rate(A!C2:C2000),
Surcharge (A!D2:D2000) and Charge Rate (A!E2:E2000). The Table and ranges
have been named.

The Interest Calculation formulas cannot use named ranged as there will be
multiple worksheets in the notebook; one worksheet for each loan.

Each Loan sheet is based on a template with 3 key sections;
* the Payment Anniversary Dates (B!E15:H15)
* the Payment Received Dates (B!C23:C29)
* the Interest Calculation Period: Period Start (B!B36:B45), Period End
(B!C36:C45)

The Period Start formula reads
IF(D36="","",IF(OR(D36+1$G$14,D36+1TODAY()),"",D 36+1))

I need a formula that will populate the Period End Date as the lesser of
i) the lookup of the Period Start Date in the Lending Rate End Date
ii) the lookup of the Payment Received Date that is less than the lookup of
the Period Start Date in the Lending Rate End Date
iii) the of the Payment Anniversary Date for the period being calculated.

Any ideas would be greatly appreciated.
--
.. . . Cheers
C.Pflugrath