1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VLookup in Excel Using Date Ranges

Discussion in 'Business Applications' started by simer, Aug 19, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. simer

    simer Thread Starter

    Joined:
    Aug 19, 2009
    Messages:
    8
    My Challenge:
    I have a table with 34 different date ranges representing individual rounds 1 to 34.

    Start End Round
    04/12/2009 05/15/2009 1

    In another worksheet I have a long "to do list" of different tasks which have start and end dates. I would like to set this sheet up so that in a "Round" column, a formula would calculate which round each task is in, given its end date.

    Task Start Date End Date Round
    Final Material Review 31-Aug-09 31-Oct-09 0



    I believe I may need to use either a VLookup or IF formulas, though from what I understand of IF formulas it would be far to complex with 34 different date ranges. I am not sure how to use a VLookup with date ranges in this senario.
     
  2. Sponsor

  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Would the round to be selected depend on the start date or the end date? Aside from that, it is fairly straight forward.
     
  4. simer

    simer Thread Starter

    Joined:
    Aug 19, 2009
    Messages:
    8
    What I would like is for the formula to select the appropriate Round, based on the end date of the task which should fit within one of the 34 rounds.

    I will be forever indebted to you if you can figure this out. For whatever reason, it is rather perplexing to me.
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Can you copy your worksheets (delete/change any data that might be confidential) and, given that the one has a long list, zip them and then post them here? (To do that, click on the "Go Advanced" button below the posting area and you will see a button marked "Manage Attachments." Click on that and you will be able to browse for your file.)
    If that doesn't work for you (sometimes newcomers to the forum haven't been able to post a file until they have 10 or so messages,) let me know and we can arrange for you to email them to me.
     
  6. simer

    simer Thread Starter

    Joined:
    Aug 19, 2009
    Messages:
    8
    I appears it will not allow me to attach anything. If you are ok with it, I could certainly email a sample document, which would contain the "rounds" list along with a shortend sample of the document I'm working with.
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    no problem - I think that inability is to prevent spamming software from attaching items. email me at loche at rocketmail dot com
    Are these two separate workbooks or two worksheets in the same workbook? While the principle is the same, it is almost always easier to keep them in the same workbook, for both have to be open in order for the references to compare,
     
  8. simer

    simer Thread Starter

    Joined:
    Aug 19, 2009
    Messages:
    8
    These are two worksheets in the same workbook, I will send it over.

    Thank you.
     
  9. simer

    simer Thread Starter

    Joined:
    Aug 19, 2009
    Messages:
    8
    Thank you!
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Glad to help. Let me (well, all of us - faster responses on here!) know if you need anything more! See you around the forum!
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/853761