+ Reply to Thread
Results 1 to 6 of 6

Find data between worksheets based on date range

  1. #1
    Registered User
    Join Date
    12-02-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Find data between worksheets based on date range

    I have been trying to come up with a formula to lookup a value in one worksheet to put into another worksheet based on a date range from a date provided in the target worksheet.

    I have two streams of data on different worksheets. In worksheet 1, I have in columns B through D: item description, Period, Cost. In Worksheet 2, I have in columns B through E: Item Description, From Date, To Date, Cost.

    I am trying to find the cost, to put in Column D in Worksheet 1, found in column E in worksheet 2 for each "item description" (in column B) that matches to the "item description" in Worksheet 2 (column B), where the Period (in column C, Worksheet 1) is on or after the From Date (column C, Worksheet 2) AND on or before the To Date (column D, Worksheet 2).

    I have attached an example worksheet for reference. I am confused if I should use VLOOKUP or SUMPRODUCT functions (or some other function for that matter).

    Thanks for your help.

    Larry

    (UPDATED 3/15/12 17:11, WITH CORRECTED SPREADSHEET ATTACHED)
    Attached Files Attached Files
    Last edited by leffler; 03-15-2012 at 09:56 PM. Reason: incorrect spreadsheet uploaded. Correcting.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Find data between worksheets based on date range

    Your example workbook doesn't tie in with your description above, as it only contains one sheet - I suspect that is an example workbook from some other Forum as it shows how (in row 4) you can identify the row that a match occurs on within a range.

    Please attach a workbook that shows YOUR data.

    Pete

  3. #3
    Registered User
    Join Date
    12-02-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Find data between worksheets based on date range

    Pete,

    Thanks for catching that. Your're right. I pulled that from another site trying to test different approaches. Attached is the correct example.
    Also, note that "Worksheet 2" I put on one worksheet in the example so as to make reading the data easier.

    Larry
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Find data between worksheets based on date range

    Hi Larry,

    I've put this formula in D4 of Sheet1 in the attached workbook:

    =SUMPRODUCT((Sheet2!B$2:B$14=B4)*(DATE(LEFT(C4,4),RIGHT(C4,2),1)>=Sheet2!C$2:C$14)*((DATE(LEFT(C4,4),RIGHT(C4,2),1)<=Sheet2!D$2:D$14)+(Sheet2!D$2:D$14="")),Sheet2!E$2:E$14)

    and then copied it down. I've moved the data for the second sheet into Sheet2, so that you can see the references.

    This assumes that you have no overlapping date ranges on Sheet2.

    Hope this helps.

    Pete
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-02-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Find data between worksheets based on date range

    Pete, it works great. I noticed in my full data set where there were duplicate entries when referencing only the first column in worksheet 2. That ended up doubling some of the cost data. I just need to figure out a way of making the reference data unique. Otherwise, it works perfect.

    Thank you!

    Larry

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Find data between worksheets based on date range

    Hi Larry,

    if you amend the formula to this:

    =SUMPRODUCT((Sheet2!B$2:B$14=B4)*(DATE(LEFT(C4,4),RIGHT(C4,2),1)>=Sheet2!C$2:C$14)*((DATE(LEFT(C4,4) ,RIGHT(C4,2),1)<=Sheet2!D$2:D$14)+(Sheet2!D$2:D$14="")))

    i.e. remove the last range, then it will give you a count of the entries that match. If you have more than one this means that you have dates that overlap, so it will help you to locate them more easily. Once you have changed them all then you can change the formula back to the one that I gave you originally.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1