Multiple lookup between two dates
 

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

Multiple lookup between two dates
 

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

Multiple lookup between two dates
 

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