+ Reply to Thread
Results 1 to 12 of 12

Lookup Function Needed

  1. #1
    Registered User
    Join Date
    12-22-2014
    Location
    Nebraska
    MS-Off Ver
    2010
    Posts
    16

    Lookup Function Needed

    I am hoping someone can please help me! How would I write the formula to do this? If Route # = 002 and Route Time = late and Days <> M-F, vlookup W-TH on another tab and subtract that value from 15 and enter it in the PM row.

    In the end I want rows 1 and 2 to say 15 in the number of days column. I want rows 3 and 4 to say 9 in the number of days column and row 5 should say 6. These numbers are because there are 15 billable days in December. I have tried the two following versions of formulas and neither returned the correct result. If anyone can tell me how to fix these or suggest another way I would be extremely appreciative!!

    =IF(AND(A2:A918=A6,C2:C918<>"M-F"),(15-VLOOKUP(and(A2:A918=A6,C2:C918<>"M-F"),'LR Days'!A1:B17,2,FALSE)),VLOOKUP(C6,'LR Days'!A1:B17,2,FALSE))
    =IF(COUNTIFS(A6:A921,A6,B6:B921,"LATE",C6:C921,"<>M-F"),(VLOOKUP("M-F",'LR Days'!$A$1:$B$17,2,FALSE)-VLOOKUP(Summary!C6,'LR Days'!$A$1:$B$17,2,FALSE)),VLOOKUP(C6,'LR Days'!$A$1:$B$17,2,FALSE))


    Route # Route Time Days Start Time End Time Number of Days
    001 AM M-F 6:30 8:45 15
    001 PM M-F 14:50 16:30 15
    002 AM M-F 6:50 8:45 9
    002 PM M-F 3:50 16:37 9
    002 LATE W-TH 17:00 18:05 6

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Lookup Function Needed

    Not surprisingly, a formula that length is a bit difficult to visualise....

    Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    12-22-2014
    Location
    Nebraska
    MS-Off Ver
    2010
    Posts
    16

    Re: Lookup Function Needed

    I have attached a sample version of my spreadsheet. The data in columns P & Q are for vlookup purposes if needed. Ultimately, all of the rows for route 1 will say 15 in the number of days column. The AM and PM rows of route 2 should say 9 and the LATE row should say 6 (and etc for the other routes).
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Lookup Function Needed

    Okay, let's spell out your criteria for different results. In your original post, you were subtracting 2 lookups if the value in B was "Late" and C was "M-F".

    If Col B = "Late" it's an automatic 6 days?
    Otherwise, simply use the VLOOKUP table in P and Q to get a value?
    Does Column A, "route" come into it at all? If so, how?
    Thanks for the clarifications
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    12-22-2014
    Location
    Nebraska
    MS-Off Ver
    2010
    Posts
    16

    Re: Lookup Function Needed

    No, it's not an automatic 6 days. It depends on what days the 'late' portion is. i.e. M-TH is 12, T-W is 6

    If all portions of the route run M-F, yes simple vlookup with P and Q.

    Yes the route comes into as I bill based on total route time.

    example - route 2 runs the AM, PM, and late only on Wednesdays and Thursdays - 6 days total. The other 9 days it runs the AM and PM portions only. I bill based on total time, so on W & TH, it's a total of 7 hours and 14 minutes while on M, T, and Fr it is 5 hours and 47 minutes.

    Does this make sense? Is it possible to write a function for this?

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Lookup Function Needed

    Okay, I can see you're getting those times. Then do you want those times multiplied by the days obtained from P and Q

    Do you just want one time per route? ie in G2 it will sum the AM, PM and Late Hours for 15 days?

  7. #7
    Registered User
    Join Date
    12-22-2014
    Location
    Nebraska
    MS-Off Ver
    2010
    Posts
    16

    Re: Lookup Function Needed

    Yes in my master workbook I have additional columns to calculate the cost. I just need something to calculate the days correctly. I am trying not to have to do any manual entry on this going forward, but can for this if I have to. So rows 2 to 4 in column G it should say 15, rows 5 and 6 in column G it should say 9, row 7 in column G it should say 6 and so on.

  8. #8
    Registered User
    Join Date
    12-22-2014
    Location
    Nebraska
    MS-Off Ver
    2010
    Posts
    16

    Re: Lookup Function Needed

    I have attached another version of my spreadsheet as well. I created a pivot table of the data which may be easier to work with. I am still looking for the same function to calculate the number of days, but this way it summarizes the routes. The pivot table is on sheet 2. So in column D, row 2 should be 15, row 3 should be 9 and row 4 should be 6. Not sure if this makes it easier or not, but I have this in my master workbook to summarize the costs per route (in additional columns that are excluded from what I have uploaded) so if it is easier to calculate here that works with my master workbook.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Lookup Function Needed

    Okay, take a look at the values I got for column G (I didn't use your pivot table). Let me know which ones are wrong, what you'd expect them to be and why. Thanks. Getting closer.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-22-2014
    Location
    Nebraska
    MS-Off Ver
    2010
    Posts
    16

    Re: Lookup Function Needed

    Okay, this is awesome! I added column G to the pivot table on sheet 2 and averaged the number of days, the only one that is wrong would be route 1. Can we make it so that all rows for route 1 equal 15?

  11. #11
    Registered User
    Join Date
    12-22-2014
    Location
    Nebraska
    MS-Off Ver
    2010
    Posts
    16

    Re: Lookup Function Needed

    Actually, if in my pivot table I summarize column G by max rather than average, it all works out! Thank you so very much! Happy Holidays!!

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Lookup Function Needed

    Glad I could help. Happy Holidays.

+ 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. Help needed for Lookup Function
    By shlomi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-23-2014, 01:09 PM
  2. Lookup function help needed
    By dimthelights in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-22-2014, 01:22 AM
  3. [SOLVED] Lookup function, help needed
    By ChristopherH in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-04-2013, 07:40 AM
  4. Lookup function needed?
    By KeiranMac in forum Excel General
    Replies: 3
    Last Post: 09-30-2009, 05:50 AM
  5. [Help Needed] Custom Lookup Function
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-10-2005, 09:05 PM

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