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

**MoonAlert** · 12-11-2012, 01:22 PM

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!

**tigeravatar** · 12-11-2012, 01:50 PM

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:

=IF(COUNTBLANK(F2:F5)>0,"",IF(OR(COUNTIFS(A2:A8,">="&F3,A2:A8,"<"&F3+1,C2:C8,F2,B2:B8,F4)=0),"No matches",INDEX(A2:A8,MATCH(1,INDEX((C2:C8=F2)*(B2:B8=F4)*(INT(A2:A8)=F3)*((ABS((A2:A8-INT(A2:A8))-F5))=SMALL(INDEX((C2:C8=F2)*(B2:B8=F4)*(INT(A2:A8)=F3)*(ABS((A2:A8-INT(A2:A8))-F5)),),ROWS(A2:A8)-COUNTIFS(A2:A8,">="&F3,A2:A8,"<"&F3+1,C2:C8,F2,B2:B8,F4)+1)),),0))))

Does that work for you?

**MoonAlert** · 12-12-2012, 08:46 AM

Hi tigeravatar,

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

Thank you very much!
Regards

**tigeravatar** · 12-12-2012, 11:36 AM

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