+ Reply to Thread
Results 1 to 8 of 8

Loookup on multiple date range & multiple lines

  1. #1
    Registered User
    Join Date
    10-17-2017
    Location
    Chicago,IL
    MS-Off Ver
    Chicago,IL
    Posts
    4

    Loookup on multiple date range & multiple lines

    Hello - I'm a beginner in Excel so my question may be very simple. I've gone through several threads for the past 45 minutes and cannot find one like this. I believe I may not need a Vlookup or an IF but possibly a INDEX and Match nested formula. I would be eternally grateful if you could please assist me.

    I have data of a few volunteer's actual spend amounts and need to calculate the variance from the budget, so who over spent. Budgetary amounts are set based on the date of the year and location. Some locations can have from 1 - 6 date ranges with different amounts and some locations can have just 1.

    This table right here shows an example of what the final result should be on the Budget column:
    Help_Result_1.JPG

    Sheet A - This is the reference tab with all the facts. Notice the date ranges for the locations.

    Help_Result_3.JPG
    Sheet B- This show what the volunteers spent, their result. This is where I build my report. Each volunteer will have 1 line only.

    Help_Result_2.JPG


    Thank you for your time
    Attached Files Attached Files
    Last edited by BrazenBigfoot; 10-17-2017 at 11:05 PM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Loookup on multiple date range & multiple lines

    welcome to the forum. do upload an excel sample so that we do not have to manually key in your data to do a testing.

    you have input the desired results so that we don't have to second-guess if what we are doing is correct or not. so that is great

    the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

    hopefully, i did a correct guess. otherwise, i have wasted time coming up with something that does not help you. if i really guessed it wrongly, please upload something according to my recommendations. try to use the solution i have given and we'll help to advise what went wrong.

    assuming B2 is Location, C2 is Trvl Start Date, and D2 is Trvl End Date:
    =SUMIFS(A!$D$2:$D$7,A!$A$2:$A$7,B2,A!$B$2:$B$7,"<="&C2,A!$C$2:$C$7,">="&D2)

    is it possible though that a person travels in between two periods of the budget amount? what should it be then?

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Loookup on multiple date range & multiple lines

    So, the data must be sorted correctly first:
    Data Range
    A
    B
    C
    D
    E
    F
    1
    Location
    Start
    End
    budget
    2
    Australia
    1/1/2017
    5/14/2017
    131
    Must sort table by column A and column B
    3
    Australia
    5/15/2017
    9/14/2017
    251
    4
    Australia
    9/15/2017
    12/31/2017
    131
    5
    Tokyo
    1/1/2017
    5/15/2017
    131
    6
    Tokyo
    5/16/2017
    9/15/2017
    203
    7
    Tokyo
    9/16/2017
    12/31/2017
    300

    Then this formula will work:
    =INDEX(OFFSET(SheetA!$D$1,MATCH(B2,SheetA!$A:$A,0)-1,,COUNTIF(SheetA!$A:$A,B2),),MATCH(C2,OFFSET(SheetA!$B$1,MATCH(B2,SheetA!$A:$A,0)-1,,COUNTIF(SheetA!$A:$A,B2),),1))

    Data Range
    A
    B
    C
    D
    E
    F
    1
    Volunteer
    Location
    Tvl Start
    Tvl End
    Actual
    Budget
    2
    Suzie
    Tokyo
    1/9/2017
    1/10/2017
    120
    131
    3
    Markey
    Tokyo
    4/11/2017
    4/13/2107
    240
    131
    4
    Jenny
    Tokyo
    9/6/2017
    9/8/2017
    262
    203
    My solution uses the START date as the determining range, but you could switch that in the formula to use the END date.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    10-17-2017
    Location
    Chicago,IL
    MS-Off Ver
    Chicago,IL
    Posts
    4

    Re: Loookup on multiple date range & multiple lines

    Hello,

    Thank you both for your help. You guys are miracle workers!. I have attached my Excel file and left JBeaucaire's version.
    Binishyrio - thank you so much. I don't think I used your version correctly when I plugged it in. Great question - for those that travel within 2 different ranges.

    Could the formula in the current file somehow give me a result of "1" or an "x" for those that fall within more than 1 season range so i can filter to these at the end and review them manually? This is great already so if not, then i rather leave at is.

    Thank you!
    ~ Grasshopper in training

  5. #5
    Registered User
    Join Date
    10-17-2017
    Location
    Chicago,IL
    MS-Off Ver
    Chicago,IL
    Posts
    4

    Re: Loookup on multiple date range & multiple lines

    Hi Jerry,

    Thank you so much for your time and assistance. I attached my file with your formula. Question, I'm guessing there is no way we can mark those that fall within more than one range with a "X" or "1" somehow?even on another column ?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Loookup on multiple date range & multiple lines

    This formula in G2, copied down:
    =IF(INDEX(OFFSET(SheetA!$C$1,MATCH(B2,SheetA!$A:$A,0)-1,,COUNTIF(SheetA!$A:$A,B2),),MATCH(C2,OFFSET(SheetA!$B$1,MATCH(B2,SheetA!$A:$A,0)-1,,COUNTIF(SheetA!$A:$A,B2),),1))<D2, "x", "")


    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    Volunteer
    Location
    Tvl Start
    Tvl End
    Actual
    Budget
    Alert
    2
    Suzie
    Tokyo
    1/9/2017
    1/10/2017
    120
    131
    3
    Markey
    Tokyo
    4/11/2017
    4/13/2107
    240
    131
    x
    4
    Jenny
    Tokyo
    9/6/2017
    9/8/2017
    262
    203

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Last edited by JBeaucaire; 10-19-2017 at 07:26 PM.

  7. #7
    Registered User
    Join Date
    10-17-2017
    Location
    Chicago,IL
    MS-Off Ver
    Chicago,IL
    Posts
    4

    Re: Loookup on multiple date range & multiple lines

    Hi
    it seems its not working on all all ranges ... can you walk me through this formula please ? I would greatly appreciate it ...help me understand a little bit please

    =INDEX(OFFSET(SheetA!$D$1,MATCH(B2,SheetA!$A:$A,0)-1,,COUNTIF(SheetA!$A:$A,B2),),MATCH(C2,OFFSET(SheetA!$B$1,MATCH(B2,SheetA!$A:$A,0)-1,,COUNTIF(SheetA!$A:$A,B2),),1))

    Denysse

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Loookup on multiple date range & multiple lines

    Where is it not working?

+ 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: 1
    Last Post: 05-06-2016, 08:22 PM
  2. [SOLVED] Processing multiple lines of text in range of cells which contain CR and LF
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 01-17-2015, 01:24 PM
  3. Replies: 2
    Last Post: 03-19-2014, 11:47 AM
  4. Replies: 11
    Last Post: 02-07-2013, 02:22 AM
  5. Replies: 0
    Last Post: 08-14-2012, 01:18 PM
  6. Count - multiple criteria, multiple sheets, and date range.
    By threecliffs in forum Excel General
    Replies: 6
    Last Post: 06-14-2011, 01:36 PM
  7. Filter for date range across multiple columns and multiple worksheets
    By Pugface in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-02-2011, 05:30 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