Get Value of first Pair

问题:

I have this file

Date      | Time         | Table  |      Checkin Time | Bill Number ..| .. |
7/1/2018   **18:03:48**   **6**         **18:03:48**       **4332**
7/1/2018   18:11:23       **6**         **18:03:48**       **4332**
7/1/2018   18:23:10       **6**         **18:03:48**       **4332**
7/1/2018   19:24:11         8             19:24:11           4333
7/1/2018   19:56:17         8             19:24:11           4333
7/1/2018   20:31:11        *6             20:31:11           4332*

I need to calculate Checkin Time which is 1st time of table number and bill number.
For example See one customer checkin at 18:03:48 with table number 6
His Bill number is 4332 .
So for table number 6 and Bill number 4332 chekin time will be 1st time
which is 18:03:48.
So I applied the formula:

=IF(AND(E3=E2,C3=C2,A3=A2),D2,MIN(OFFSET(E3,0,-3,MATCH(0,(E3:E$35470=E3)*(C3:C$35470=C3)*(A3:A$35470=A3),0)-1,1)))

But the value changes at 6th row .
It's giving 20:31:11 as checkin time .
It should be 18:03:48 because the 6th row table number and bill number is same as 1st one.
So How do I make it possible, to calculate the checkin time as per 1st time who have same bill and table number , no matter which row .
Here is the excel file I have uploaded to Google drive.
Here is the screenshot.

Edit:- Formula by @jeeped work, But as the log is of 30 days
The data keeps repeating the other day too.
Can it be done by giving some unique date value ?

回答1:

@Jeeped's answer is spot on, here is an adjustment for the formula to work over multiple days that have the same table numbers and bill numbers:

=AGGREGATE(15, 7, ((A$2:A$13)+(B$2:B$13))/((C$2:C$13=C2)*(E$2:E$13=E2)*(A$2:A$13=A2)), 1)

All I did was add this extra conditional argument, that checks if the "Date" is the same:

*(A$2:A$13=A2)

回答2:

If you don't have the newer MINIFS and MAXIFS, use AGGREGATE to achieve the same result.

=AGGREGATE(15, 7, ((A$2:A$7)+(B$2:B$7))/((C$2:C$7=C2)*(E$2:E$7=E2)), 1)

Format the result any way you want; as date and time, time only, etc.

回答3:

You can make use of an array formula combining INDEX+MATCH to return the earliest time value that matches your conditions first. In cell D2, enter the following:

=INDEX($B$2:$B$7,MATCH(1,($C$2:$C$7=C2)*($E$2:$E$7=E2),0))

When returning, be sure to press CONTROL+SHIFT+ENTER instead of just ENTER to make sure the formula calculates correctly.