If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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),D210) -- 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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|