+ Reply to Thread
Results 1 to 6 of 6

Formula that pulls across a value based on the which date range the given date falls into.

  1. #1
    Registered User
    Join Date
    06-13-2015
    Location
    Columbus, Ohio
    MS-Off Ver
    2013
    Posts
    13

    Talking Formula that pulls across a value based on the which date range the given date falls into.

    Hello!

    I feel like I'm close to a solution, but I can't seem to figure out an efficient solve to this. I feel that an INDEX solution may be needed, but I'm not very well-versed in INDEXes. I don't think vlookup will be helpful here either, since this only pulls the first match.

    What I'm looking to do is create a formula that exists on tab A (not shown) that works as a helper column next to raw data, that says "when employee matches column 1 and date falls between the dates in the attached table (columns 3 & 4), pull across the team name from column 2.

    To put more in context, we have sales teams where the employees transition teams every so often.
    However, we want to make sure we are assigning "credit" for sales metrics achieved by employees fairly.
    For example, if an employee just transitioned from team Thompson to team Holbrook, we want to make sure the sales metrics achieved prior to this transition are credited to team Thompson. This will be handled by assigning the correct "Team" when a metric date falls between the date range, which is essentially the dates that the employee was a part of that team.

    I'm happy to provide more details if needed.

    Thanks ahead of time!

    Chris


    tableexample.png

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

    Re: Formula that pulls across a value based on the which date range the given date falls i

    It would help if you attached a sample Excel workbook, as it is difficult to advise on a formula for tab A when we can't see how the data on that tab is laid out.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it is broken on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-13-2015
    Location
    Columbus, Ohio
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula that pulls across a value based on the which date range the given date falls i

    Thanks for the feedback Pete. I've attached a workbook so you can see the layout that I'm referring to. Once again, the raw data will be on the "data" tab, and the table I'm referencing will be on the "table" tab.

    Let me know if you have additional questions.

    Thanks!
    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
    22,509

    Re: Formula that pulls across a value based on the which date range the given date falls i

    Put this array* formula in cell C2 of the Data sheet:

    =IFERROR(INDEX(table!$B$2:$B$7,MATCH(1,(table!$A$2:$A$7=A2)*(B2>=table!$C$2:$C$7)*(B2<=table!$D$2:$D$7),0)),"")

    *Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter, rather than the usual < Enter >.

    Then you can copy it down as far as you need to using your usual method(s).

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    06-13-2015
    Location
    Columbus, Ohio
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula that pulls across a value based on the which date range the given date falls i

    Pete,

    You are the man. Thanks so much, this works perfectly!

    Chris

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

    Re: Formula that pulls across a value based on the which date range the given date falls i

    You're welcome, Chris - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

    Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 07-14-2017, 12:04 PM
  2. [SOLVED] Formula for setting a value in one cell if it falls within a certain date range
    By seanpod in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-31-2017, 02:03 PM
  3. [SOLVED] help with formula to add values up if cell falls within a date range
    By dagummit in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-11-2016, 11:03 AM
  4. [SOLVED] Formula to assign specific numbers if a date falls within a certain date range
    By headley4ever in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-24-2014, 04:16 PM
  5. Replies: 4
    Last Post: 09-12-2013, 11:32 PM
  6. Replies: 15
    Last Post: 04-08-2013, 12:40 PM
  7. Replies: 4
    Last Post: 02-18-2009, 09:21 AM

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