+ Reply to Thread
Results 1 to 4 of 4

Finding row on matrix matching 3 criteria and get a result (hour)

  1. #1
    Registered User
    Join Date
    12-11-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2010
    Posts
    7

    Question Finding row on matrix matching 3 criteria and get a result (hour)

    Hello,

    The matrix:
    Col A Col B Col C
    2012-11-08 07:26:16 Check-In John
    2012-11-08 13:23:58 Check-Out John
    2012-11-08 13:24:01 Check-In John
    2012-09-27 07:34:13 Check-In Marie
    2012-09-27 17:35:31 Check-Out Marie
    2012-09-28 07:10:16 Check-In Marie
    2012-09-28 17:34:40 Check-Out Marie

    The goal:
    Since i have a given:
    1. Name(which exists in column C)
    2. Date (which exists in column A - formated has (AAAA-MM-SS HH:MM:SS)
    3. Event (which exists in column B)
    4. Hour*

    I want to know the nearest hour that matches all criterias.

    In the example on top, if i give:
    John, 2012-11-08, Check-In, 14:00:00
    I want the result: 13:24:01

    If, for example, the date doesn't exist, the result could be a simple error message.

    Anyone can help me?

    Thanks in advance!

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Finding row on matrix matching 3 criteria and get a result (hour)

    MoonAlert,

    Welcome to the forum!
    Attached is an example workbook based on the criteria you described.
    In cells F2:F5 is where the search criteria gets entered.
    In cell F7 is this formula to get the result:
    Please Login or Register  to view this content.

    Does that work for you?
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

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

  3. #3
    Registered User
    Join Date
    12-11-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Finding row on matrix matching 3 criteria and get a result (hour)

    Hi tigeravatar,

    It seems to work like a charm!
    If I find any issue, I will back to you.

    Thank you very much!
    Regards

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Finding row on matrix matching 3 criteria and get a result (hour)

    You're very welcome

    If that takes care of your need, please mark this thread as solved.
    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:
    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ 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