The following samples are from individual sections of my woorksheet.
Save Human Error.
C10 is where my fourmula is at.
Here is part of the equation
C10 says that if B1 is less than 1/1/2005 then look at C6.
IF(AND(C61000,RIGHT(C6,3)<=large(A7:A10,1)) then display RIGHT(C6,3),
otherwise if C6<1000 then C6.

In Sample1 B1 is less than 1/1/2005, but in Sample2, B11/1/2005

Sample 1
A B C
1 date 8/29/2002 11/6/2005
2 draft 40
3 since 97 1165
4 first
5 days 97
6 line 13521 1165
7 4 3458
8 410 6798 1165
9 460 5745
10 Chec 165

In Sample2, C10 says is the same as in sample1 but because B1=1/1/2005, B5
becomes a factor.

Sample 2
1 date 7/1/2005 11/6/2005
2 draft 700
3 since 178 128
4 first
5 days 1502
6 line 21937 128
7 71 306
8 107 1518
9 700 7835 128
10 Chec 630
Therefore
C10 says that if B1<=1/1/2005 then look at C6.
IF(AND(C61000,RIGHT(C6,3)<=large(A7:A10,1)) then display RIGHT(C6,3),
otherwise if C6<1000 then C6
Otherwise,
IF(B1=1/1/2005, then look at B5 & C6.
IF(AND(B5=1000,RIGHT(B5+C6,3)<=large(A7:A10,1)) then RIGHT(B5+C6,3)
otherwise if(AND(B5<=1000,C6<1000,RIGHT(B5+C6,3)<=large(A7:A 10,1)) then
RIGHT(B5+C6,3),B5+C6<1000,B5+C6

In a nutshell
Essentially, weather B5 & C6 are together or if we are just looking at C6 ,
the ultimate return in C10 should be as close as possible to a 3 digit number
that is equal to or less than large(A7:A10,1)
It can eeven go as far as RIGHT(C6,2) just to get it within large(A7:A10,1)
Make Since!?
Luke