+ Reply to Thread
Results 1 to 4 of 4

Index, Match, Vlookup, Hlookup or what?

  1. #1
    Registered User
    Join Date
    09-18-2014
    Location
    Dallas, Texas
    MS-Off Ver
    2010
    Posts
    4

    Index, Match, Vlookup, Hlookup or what?

    OK everybody here is my issue. I make the schedule for my job and keep track of the employees that do not have a set schedule. I need a way to be able to each employee their schedule only. I used vlookup and other function to help me otimate this but I have gotten mixed feedback about the format. I finally found a format that everyone can agree on but I have no idea how to get it started.
    I attacked a sample so that you can take a look. Basically I need to be able to change the dates on the individual schedule and have excel give me (from the master schedule above) the post, start and end times the employee I type in will be working that week.
    I’m comfortable with most lookup function but I really have no idea how to even get started. Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Contributor Mayda89's Avatar
    Join Date
    09-13-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Index, Match, Vlookup, Hlookup or what?

    If this is the format of the source table you want to use, you are going to have to use Index and Match, and also the Offset function.
    -You cannot use Vlookup, because first of all, to use Vlookup, the values you want to return should be located to the right of the values to be looked up; so in this case, the "post #" and "the hours", should be to the right of the employee names, which would of course be a very impractical thing to do.
    -Secondly, just using Index and Match is also not gonna solve your problem, you're also gonna have to include the offset function, because the range you will be looking up is also dynamic.

    Anyway, I have typed the formulas in the attached workbook.

    In every table, the yellow cells should be typed in by the user, and the blue and red cells will be calculated by excel.

    Note that I have formatted the cells containing time values as "Time", otherwise the formula calculating the total time of the shift wouldn't work.
    Attached Files Attached Files
    If I have helped, Don't forget to add to my reputation (click on the star icon below the post).
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved).
    Use code tags when posting your code.

  3. #3
    Registered User
    Join Date
    09-18-2014
    Location
    Dallas, Texas
    MS-Off Ver
    2010
    Posts
    4

    Re: Index, Match, Vlookup, Hlookup or what?

    Mayda89,

    Thanks for the help this is exactly what I was looking for and after adapting it to my schedule it is working perfectly.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index, Match, Vlookup, Hlookup or what?

    you don't need offset
    =INDEX($A$2:$A$10,MATCH($F$15,INDEX($D$2:$Q$10,0,MATCH(B18,$D$1:$Q$1,0)),0))
    will do
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ 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. [SOLVED] Index, Match, Vlookup, Hlookup help needed
    By Gemma_10 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2013, 01:05 PM
  2. Vlookup with Hlookup or Index with Match?
    By brad_x81 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-08-2013, 06:40 AM
  3. Vlookup, HLookup, Index, Match, oh my! Help me.
    By ryanl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-23-2013, 09:29 PM
  4. Vlookup/hlookup/match/index?
    By margggggg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2012, 07:46 PM
  5. [SOLVED] Vlookup & Hlookup at same time - Why is this Index+Match formula not working
    By fabrice.usa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2012, 06:46 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