+ Reply to Thread
Results 1 to 10 of 10

Lookup formula to match Employee ID & Date and input a value from a separate Excel sheet

  1. #1
    Registered User
    Join Date
    06-22-2012
    Location
    New Brunswick, NJ
    MS-Off Ver
    Excel 2003
    Posts
    53

    Lookup formula to match Employee ID & Date and input a value from a separate Excel sheet

    Hi,

    I have been using an Index Match function to lookup Employee ID's in one sheet and match them to a look up table in order to fill in the Employee's respective department, division, etc. information.

    Now I want to do that to fill in hours, only problem is I need to take into account dates because each employee can have multiple rows of the same date due to different time codes.

    I want to lookup and match Employee ID & Date so the hours from one sheet can be correctly dumped into a more trued up version of hours that are in another sheet.

    Any help would be greatly appreciated, I attached a sample sheet below. Thank you in Advance!!
    Attached Files Attached Files

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2003-2010-2013
    Posts
    2,078

    Re: Lookup formula to match Employee ID & Date and input a value from a separate Excel she

    try this formula in cell I2 and copy it down.
    Remark that this is an ARRAY formula and that you have to end its editing by clicking at CTRL+SHIFT+ENTER at the same time.
    Hope this is what you where looking for. The principle being to concatenate the different column of informatin you want to look for in the "Lookup value" and in the "Lookup array".
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup formula to match Employee ID & Date and input a value from a separate Excel she

    Quote Originally Posted by p24leclerc View Post
    try this formula in cell I2 and copy it down.
    Looks like you forgot to post the formula!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    06-22-2012
    Location
    New Brunswick, NJ
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Lookup formula to match Employee ID & Date and input a value from a separate Excel she

    Hey can you follow up with that formula, I have tried using other options but still stuck on how to get everything to pull through accurately.

  5. #5
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2003-2010-2013
    Posts
    2,078

    Re: Lookup formula to match Employee ID & Date and input a value from a separate Excel she

    Here is the formula.
    Please Login or Register  to view this content.
    Don't forget to hit CTRL+SHIFT+ENTER when you are done editing it.
    Sorry for the delay.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup formula to match Employee ID & Date and input a value from a separate Excel she

    I downloaded your file but I'm not sure what results you expect.

    For example...

    For 750070 on 9/5/2014, there are 3 instances for those criteria but there is just a single number value that corresponds to the 2nd instance. If you do a lookup on 750070 and 9/5/2014 the basic formula will find the first instance and that instance has an empty corresponding cell.


  7. #7
    Registered User
    Join Date
    06-22-2012
    Location
    New Brunswick, NJ
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Lookup formula to match Employee ID & Date and input a value from a separate Excel she

    Thats the trouble I'm having. I want to have a formula that will sum all hours for that Employee for that day from the 2nd sheet and input them into the first sheet. I feel like it is some combination of sumifs and index/match.

    The premise of this I receive an hours sheet say for April hours in May, but the April hours aren't finalized until June so I receive a true up.

    But the one column that will not change is the old FMLA hours column those cannot change ever. But the new report now has 70,000 rows for April hours instead of 50,000 rows so it cant just be a copy and paste them over type of thing, I need to just an index to match the right FMLA hours to that appropriate employee on that specific date.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup formula to match Employee ID & Date and input a value from a separate Excel she

    Quote Originally Posted by cajodonn View Post
    I feel like it is some combination of sumifs and index/match.
    SUMIFS will work:

    =SUMIFS('Old Hours File'!I$2:I$7,'Old Hours File'!A$2:A$7,A2,'Old Hours File'!D$2:D$7,D2)

  9. #9
    Registered User
    Join Date
    06-22-2012
    Location
    New Brunswick, NJ
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Lookup formula to match Employee ID & Date and input a value from a separate Excel she

    I tried this before, but the reason it doesn't work if because there's different time codes that can appear for the same day of work. I'm sorry its hard to explain on here because I know you guys get verify the results without really knowing the data. I do appreciate the help and your time.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup formula to match Employee ID & Date and input a value from a separate Excel she

    So then the time code is a third criteria?

    Lookup employee number + date + time code?

+ 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] Gathering data from separate sheets based on employee name & date
    By avidcat in forum Excel General
    Replies: 14
    Last Post: 07-14-2014, 08:02 PM
  2. [SOLVED] Match Multiple Columns from Separate Sheet to get separate column.
    By gvitoro in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2014, 03:38 PM
  3. How to transfer data from a daily input sheet to a separate monthly total sheet
    By Jcooper71 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2014, 02:37 PM
  4. [SOLVED] Employee lookup, index match
    By nickmessick1 in forum Excel General
    Replies: 3
    Last Post: 07-03-2013, 12:02 PM
  5. [SOLVED] Returning the newest date entry for an employee on a separate sheet
    By avidcat in forum Excel General
    Replies: 4
    Last Post: 09-14-2012, 07:34 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