+ Reply to Thread
Results 1 to 7 of 7

Lookup with multiple criteria and matches

  1. #1
    Registered User
    Join Date
    01-26-2013
    Location
    Alaska
    MS-Off Ver
    Excel 2010
    Posts
    5

    Lookup with multiple criteria and matches

    I have data about login & logout times by agent. I need to be able to see it by agent name so I can decipher if they came in late, left early, or even logged in that day. I also need to see multiple references if they exist (logged in at 3pm - out at 3:30, in at 3:40), or if they log in on day 1, and log out on day 2 (logged in at 4pm day 1, out at 1am day 2)

    I've been working with the Index function but I'm not matching against the correct reference, it appears to be matching against the first row and going down - which shows my ignorance with index, small, and row.
    =INDEX($A$9:$C$125,MIN(IF($B$9:$B$125=$G$7,ROW($B$9:$B$125)),ROW(1:1)),1)

    I am assuming that is because the actual data I want to match is in row 13, and i'm starting at 1, but thought it would start the match by the agent name.
    Attached Files Attached Files

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

    Re: Lookup with multiple criteria and matches

    hi pmeans, welcome to the forum. so the table on the right is the exact same data as the data on the left? try to use the same headers from G2:J2, then try this array formula in G3:
    =INDEX($A$2:$E$26,SMALL(IF($B$2:$B$26=$G$1,ROW($B$2:$B$26)-ROW($B$2)+1),ROWS(G$3:G3)),MATCH(G$2,$A$1:$E$1,0))

    some other names might have more or less data, so add a IFERROR if you like:
    Please Login or Register  to view this content.

    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 bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Lookup with multiple criteria and matches

    Try in G3:
    Please Login or Register  to view this content.
    Confirmed by holding both Ctrl-Shift, then Enter
    Copy to H,I,J by replacing 1 with 3,5,4
    Copy down.
    Quang PT

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Lookup with multiple criteria and matches

    Is there a reason you are not using Filter or Advanced Filter?
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    01-26-2013
    Location
    Alaska
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Lookup with multiple criteria and matches

    Yes, that seems like the simple answer. I need to share this with another supervisor, and I need it for backup if I make changes to timecards. So i'd prefer a method that lets me type in the employee name (I have 30), and then use the current 2-week period to double-check what they put down for time.

  6. #6
    Registered User
    Join Date
    01-26-2013
    Location
    Alaska
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Lookup with multiple criteria and matches

    Thank-you this works great! - What kind of Excel training should I be looking for that covers this type of formulas?

  7. #7
    Registered User
    Join Date
    01-26-2013
    Location
    Alaska
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Lookup with multiple criteria and matches

    THIS was the Most helpful solution - thank-you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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