Hi,
I have a ratesheet in excel like e.g.

Carrier FromDate  ToDate        Rate
A          01.02.10.  28.02.10.   100
A          01.01.10.  14.01.10.   99
A          15.01.10.  31.01.10.   98
B           13.01.10. 31.10.10.   101
C          16.01.10.  21.02.10.   97
A          13.12.09.  28.12.09.   101
etc.

The date for respective carrier are not overlapping. So in the rate sheet 
will never happen, that a rate is valid for carrier A fm 01.01.10. till 
31.01.10. and another rate for the same carrier A fm 15.01.10. till 31.01.10. 
In such case the previous one will be valid just till 14.01.10.

I need a formula if e.g. I wd like to find what rate was valid on 18.01.10. 
for carrier A (in this example it wd be in third row the rate 98). 

Tks for any help !
Ales

Assuming carrier is in cell E2, date is in F2, something like this:

=SUMPRODUCT(--(A2:A10=E2),--(B2:B10<=F2),--(C2:C10>=F2),D2:D10)
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ales" wrote:

> Hi,
> I have a ratesheet in excel like e.g.
> 
> Carrier FromDate  ToDate        Rate
> A          01.02.10.  28.02.10.   100
> A          01.01.10.  14.01.10.   99
> A          15.01.10.  31.01.10.   98
> B           13.01.10. 31.10.10.   101
> C          16.01.10.  21.02.10.   97
> A          13.12.09.  28.12.09.   101
> etc.
> 
> The date for respective carrier are not overlapping. So in the rate sheet 
> will never happen, that a rate is valid for carrier A fm 01.01.10. till 
> 31.01.10. and another rate for the same carrier A fm 15.01.10. till 31.01.10. 
> In such case the previous one will be valid just till 14.01.10.
> 
> I need a formula if e.g. I wd like to find what rate was valid on 18.01.10. 
> for carrier A (in this example it wd be in third row the rate 98). 
> 
> Tks for any help !
> Ales
> 

tks Luke !!
Needed half day experimenting why the formula returns "N/A" and how to make 
the formula if the worksheet has different rows (next month more) . The "N/A" 
reason was that at the end of my 2000 rows in column A there were few "N/A" 
which I did not see initially and which caused the formula to return "N/A".  
When I deleted the "N/A"-rows works now. Tks a lot !

Ales