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

0
Utf
2/9/2010 4:58:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
2409 Views

Similar Articles

[PageSpeed] 3

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
> 
0
Utf
2/9/2010 5:16:01 PM
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
0
Utf
2/10/2010 4:32:01 PM
Reply:

Similar Artilces:

Query/Report date parameter
Hi folks, I have a problem which, I hope someone can help me. I have a table that have the following fields: ID Date Supplier name Merchandise type T01 T02 T03 PS: T01,T02 and T03 are error type feild, which I can enter quantity of units affected. Now I need to have a report that show me the grand total of each error types ("T01,T02 and T03), the report will need to have a date paramater. I've tried, but each type its only giving me the grand total of error by date and not the grand total by error types, because I am adding the following parameter in the query "Between [Star...

emailing multiple recipients
Trying to send the same email to multiple people through outlook express or Act with only one recipient being shown at one time. How do I do this. Thank you. ...

How do I email blind "To" (multiple recipients) copies?
I need to email blind "To" (multiple recipients) copies (not bcc)? How do I do that? LTI wrote: > I need to email blind "To" (multiple recipients) copies (not bcc)? > How do I do that? What has this to do with MS Publisher? -- In memory of MS MVP Alex Nichol: http://www.dts-l.org/ apparently you're not all that familiar with MS Publisher. If you were, you would know that you can email your web or email page to recipients. "Miss Perspicacia Tick" wrote: > LTI wrote: > > I need to email blind "To" (multiple recipients) cop...

mistaken date entry
I was catching up on balancing my checking account (Money 2005). I accidentally accepted the default date (Oct 8th) when it should have been Aug 8th. How can I go back and change that date so I can continue with the correct date? "IsisTheCat" <isisthecat47removeme@hotmail.com> wrote on 11 Oct 2007 in group microsoft.public.money: > I was catching up on balancing my checking account (Money 2005). I > accidentally accepted the default date (Oct 8th) when it should have > been Aug 8th. How can I go back and change that date so I can > continue with the ...

Multiple line charts on the same graph
Let's say that I have 10 columns of data. Column A represents "origination" dates and Column B represents "total revenue" for those dates in Column A. Column C represents other origination dates and Column D represents total revenue for each of those dates in Column C. Etc for Columns E/F, G/H, and I/J. I know how to create a Line Graph for A/B. But how do I create FIVE lines (graphs), each representing A/B, C/D, E/F, G/H, and I/J, respectively, and all appearing on the SAME chart? Thanks! Use a scatter XY chart with lines and setup your data like this (sh...

Outlook Views & Filters
We're using Exchange public folders & Outlook with various languages. Our views were setup in English and it seems Outlook views are language specific. Views setup with criteria (filtering views) in English fail to work properly on a German Windows machine. I have not located any posts on this issue. Can anyone provide some help or a solution? Thanks! Fred ...

Top 10 Free Dating Web Sites and much more for free.
Top 10 Free Dating Web Sites and much more for free. You want Free Date then go and search on http://www.10sites.uni.cc Every thing you need is Free here on http://www.10sites.uni.cc http://www.10sites.uni.cc ...

date of JPG photo file which is taken
hi, i am using a lot of photos taken at a construction cite,and i have to write the date of JPG file which is taken to excel sheet. how can i get the date VBA property of which is taken at cite. I mean the date of taking photo thanks Go here: http://sourceforge.net/projects/exifclass/ Download the (zipped) source code - extract the "cls" file and import to your VBA project (it's for VB6 but seems to work fine in Excel VBA). Example usage: Sub Tester() Dim exif As New ExifReader exif.Load "D:\Pics\2009\CIMG1730.JPG" Debug.Print...

Default Unit of measure / Multiple unit of measures for Product
Hi, We have Products that have more than one unit of measure. On the product page - the text for unit of measure is "Default Unit". Question: Is there a way to have multiple unit of measures for the product ? Has someone accomplished this using a custom soulution. I have ideas of creating another entity and linking it with a one to many relationship, but I want to see if there is a better way to make this work. Goal: Product has more than one unit of measure - lb, ft User on order page can select default unit of measure or one of the alternate unit of measures. Thanks J Singh ...

Tricky formatting and Date Formula
I need to calculate the difference in ages for a school tracking program. The problem is in the fact that the ages are recorded as follows: 6.1 is a child 6 years and 1 month 6.2 is a child 6 years and 2 months etc 6.10 is a child 6 years and 10 months 6.11 is a child 6 years and 11 months The formatting cannot differentiate between 6.1 and 6.10. Is it possible to get around these problems so that I can a) record the ages so that they mean something in the spread sheet B) create a formula that will work out the age differences between a chronological age of, for example, 8.1 (8 years an...

sumnumbers based on two criterias
Hello, Need your help to sum a range of numbers based on two criterias. eg. A1 to A4 contains number 5,5,6,7 BI to B4 contains text A,B,C,B C1 to C4 contains text I,II,II,II I wanted to sum cells in column A, where B:B = C and C:C = II (in this result =12) For one criteria i could do it with Sumif function. Please help! Jimmy Joseph =SUMPRODUCT(--(B1:B4="C"),--(C1:C4="II"),A1:A4) Bob Umlas Excel MVP "Jimmy Joseph" <jimmy.joseph@in.bosch.com> wrote in message news:Xns974F63E725A21jimmyjosephinboschco@client.loc.cc.bosch.com... > Hello, >...

Two Personal Folders in Outlook 2003
I moved my .PST file from my old PC that was using Outlook 98 to my new XP driven PC that uses Office and Outlook 2003. I had to rename the .PST from PERSONAL to OUTLOOK in order to get it work. Trouble is there are two (2) PERSONAL FOLDERS in Outlook 2003 that are identical, except for different icons. One icon looks like a "house", the other looks like a "card or address file". Any suggestions on how to get rid of one of these would be greatly appreciated. I'm over my head, and Microsoft's support areas are no help. Thanks. Ben Collins If you can&#...

How do I merge two files in Excel?
How do I merge two Excel files, each with a different name and different data? Thanks...Mike Hi you may provide some more details: - data layout - HOW you want to merge them - etc -- Regards Frank Kabel Frankfurt, Germany "Chem303" <Chem303@discussions.microsoft.com> schrieb im Newsbeitrag news:BACC8208-C906-4B80-A3B7-6DFD260C00A4@microsoft.com... > How do I merge two Excel files, each with a different name and different > data? Thanks...Mike ...

MEET & DATE MORE LADIES EASILY (Proven) sssssssssssssssss 374 [1/7]
SEXUALLY ATTRACT WOMEN INSTANLY WITH PHEROMONES Even the most impossible to get beautiful women become Possible dates, lovers and even wives with Pheromones Pheromones are nature's sexual scent, NOW in a cologne to attract women PHEROMONE SEX ATTRACTANT FOR MEN THE COLOGNE FOR THE Y2K MAN AVAILABLE IN THE U.S. AND PERFECTLY LEGAL ATTRACT WOMEN INSTANTLY with Nature's secret weapon.... PHEROMONES... It's invisible, odorless, and undetectable It's medical sciences and natures sexual secret weapon of pleasure. This is a perfectly legal sexual ...

Why is WMP 12 putting two of everything in my library?
In my music library I have doubles of every song, not the albums, just the songs. I've tried changing search options to no avail. I have a removable disc attached with backups of everything on it that is excluded from the search. "Seanpablo" wrote: > In my music library I have doubles of every song, not the albums, just the > songs. I've tried changing search options to no avail. I have a removable > disc attached with backups of everything on it that is excluded from the > search. I have disconnected the removable drive and emptied t...

Changing Dates
I am trying to figure out a way to auto-roll the dates on a spreadsheet forward. Basically, what I have is this: A1 B1 C1 D1 09/25 10/01 10/08 10/15 etc... And what I want is for those dates to all roll-forward as they are surpassed, so that on 09/26, the dates will increment one week and show: 10/01 10/08 10/15 10/22 etc, forever. I appreciate any help in sorting this out. ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Far...

Lists, lookup, if???
Hi, A new user to this site. Been learning a lok lately, but stuck now! I have a sheet where i want to look for "value1" to "value10" in columns I, K, M, O. Once a particular value is found, I want it to display the value (numerical) in the right hand column, J, L, N, P. But I also want it to count how many parts are require by looking at the number enterred in the same row but column D. Basically, it's a cutting list with different profiles, lengths and quantities and I'd like a summary of each profile, with each cut length required and qua...

Mail delete date
Can I sort the items in delete folder in deleted date? I don't think you can. I'm not sure if deleted date is a field Outlook maintains. "Lok Tak Cheong" <tc.lok@adckrone.com> wrote in message news:uMvGfxPkFHA.2484@TK2MSFTNGP15.phx.gbl... > Can I sort the items in delete folder in deleted date? Yes you can. �f you add "modified" column and you didn't change item then deleted you can sort item in modified date (~deleted date). Evren Arslan "Lok Tak Cheong" <tc.lok@adckrone.com> wrote in message news:uMvGfxPkFHA.2484@TK2MSF...

How do I set the date to change into the right format?
I need to be able to type 15/06/06 or 150606 and for it to change to 15-jun-06 when I leave the cell. I have tried all kinds of things, and usually, it stays as I typed it, or it shows like I type it mm-dd-yy. Can someone please tell me how to format the date cell properly? I have so many cover sheets to type for the month end...!Thank you! Did you try to format your cells to the format you want, in your case Format/Cells/Date/[*chosse*]? Henk "Tab In The Office" wrote: > I need to be able to type 15/06/06 or 150606 and for it to change to > 15-jun-06 when I leave t...

Date field format
The date field appears to require the day, month, and year. I'm setting up a catalogue of items where there is an "acquired date" field. Some items have day, month, year and some items have month, year and some items have year acquired only. Is there a way to keep it as a date field but only enter part of the known dates? If not, then I assume it has to change to a text field. If that is the case is there a "better" way to enter the dates so that if needed, it could be queried or sorted as a date field? Examples coming to mind: year-month-day: 2009-...

Moving numbers to two decimal places i think...
well heres my problem- im trying to copy a "total" price from exce into a vbe using a controlsource, the problem is in excel it display �593.01 but in vbe i get 593.009, how could i set it up to display pound sign and take it to the nearest two decimal places -- 4nd ----------------------------------------------------------------------- 4ndy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=26585 Hi 4ndy, Sub Tester() Dim MyVar As Double MyVar = Sheets("Sheet1&qu...

Two Separate ID Addresses from Microsoft Outlook
I have multiple e-mail addresses. I would like to send and receive e-mails from two or more e-mail addresses from the SAME OUTLOOK program on my desktop. I have Windows 98. Thank you, Walt Vignault at 770 565-0414 On Tue, 11 May 2004 13:01:03 -0700, "vignault@comcast.net" <anonymous@discussions.microsoft.com> wrote: >I have multiple e-mail addresses. I would like to send >and receive e-mails from two or more e-mail addresses from >the SAME OUTLOOK program on my desktop. I have Windows 98. > >Thank you, >Walt Vignault at 770 565-0414 you don't say...

Import records w/ date fails
Does anyone know the correct format for date fields during import? I'm attempting to import a file into contacts and leads that have date fields, but I keep getting an error and the records will not import. I've tried variations of date formats like: 01/01/02 01/01/2002 20000101 Any help would be most appreciated... I know I'm missing something small... Thanks!! Thanks in advance!! I just figured it out. Dates have to be imported as YYYY- MM-DD format. >-----Original Message----- >Does anyone know the correct format for date fields during >import? I'm...

Problems linking the fields in two forms
I created a command button on my form so that I can open up another form to save space and make it look neater on my main form. The common field between these two forms is the PO (Purchase Order) number but the subform that opens up does not autopopulate that same number from the primary form - how can I fix this? -- Kenji Could you explain how you open the secondary form (not subform)? Macro? What action(s)? Code? What lines? -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Changing default filtered on date for activities and history
Hello, I am trying to change the default filtered on date in activities to all, as it is defaulted right now to [activities next 30 days] [history last 30 days] Any advise on this would be appreciated. Thanks Did you manage to get this solved or an answer? I would be quite keen to know the answer to this. There is no proper way of do this, but I have had lots of people asking if it is possible. I have found a work around here (http://www.unitek.com/training/microsoft/crm/blog/2008/01/18/changing-the-default-views-in-crm-30-crm-40/) but have to say I have not tested this yet. Hope it a...