How to find max value of a column which is less than a cell?

[ObiObi]

I have two datasets, which are login and logout times. Not everybody who logs in actually logs out, so the logout dataset is smaller than the login dataset. Each login and logout has a sequence ID, which is monotonically increasing across all logins and logouts of a user (but is reset to 1 every day).

I assume that the login corresponding to a logout is the one which have (a) the same user ID, (b) are of the same date, and (c) whose sequence ID is the largest, but is not larger than that of the logout entry.

Here is a sample login dataset (columns A-D):

Please Login or Register  to view this content.

Here is a sample logout dataset (columns H-K):

Please Login or Register  to view this content.

My attempt so far has generated this formula in column L:

=SUMPRODUCT(($C$2:$C$12=J2)*($B$2:$B$12=I2)*($D$2:$D$12<=K2)*($A$2:$A$12))

The first and second parts of the sumproduct makes sure I am looking at the correct user and the correct date. The last one picks the number of the matching login entry. I am having trouble with the middle part, which picks the "max of login seq for the user on the date, but not larger than logout seq of the same user". The way it stands, it simply adds up all the numbers. I try to put a MAX() function there, the maximum of the entire column is used. I have been breaking my head here for a while now.

Please help.

[Kaper]

isn't it enough just to use MAX rather then SUMPRODUCT but .. commit it as array formula (with Ctrl+Shift+Enter not just Enter alone):

Please Login or Register  to view this content.

PS. next time rather attach sample workbook than copy text from it's contents.

[ObiObi]

isn't it enough just to use MAX rather then SUMPRODUCT but .. commit it as array formula (with Ctrl+Shift+Enter not just Enter alone)

My other research also suggested such an approach, but I didn't quite get it right.

PS. next time rather attach sample workbook than copy text from it's contents.

Here is a sample workbook. Apologies for not attaching it earlier. What I need is the formula in column L to reflect the manual matching I have done in column M. Take the example of the highlighted rows - on the right-hand (logout) side, user A on 1/1 has Seq of 4. On the left-hand (login) side, user A on 1/1 has two Seqs - 1 & 3. The match should be the largest of these which is smaller than 4. The required output is the number from column A corresponding to the match (row 5, No = 4).

Edit: It is guaranteed that for a given user on a given day, all the Seqs are unique.

[Kaper]

I still think MAX committed as array formula is right.
See attached with:

Please Login or Register  to view this content.

CSE, not just Enter!
note two green rows - there is the same data, so the output in column L is the same. but in column M you gave different values (red cells) - probably because your sample data is not unique - see rows 6 and 11 in columns B:D.

[ObiObi]

Thanks for that. It works now. I was doing the MAX only for the third array, which was the mistake.

My actual data has 100K+ rows, and it takes hours for this formula to produce data on a i5 laptop. But such is the price...

[Ace_XL]

Non-array alternative

=MAX(INDEX(($C$2:$C$12=J2)*($B$2:$B$12=I2)*($D$2:$D$12<=K2)*$A$2:$A$12,),0)

Should reduce processing time for sure!

[Kaper]

Should reduce processing time for sure!

Could be, but as a matter of fact it is anyway an array formula, even if it is not CSE one.
So could be somawhat quicker, but not super-quick?
As a matter of fact, I tried both methods (not run proper tests) with the same data as in sample file copied down 20K rows.
Wrote first formula in row 2, copied down - recalculation (also i5 laptop) went roughly 1-2% per second. deleted whole colun, wrore second formula in row 2, copied and ... very similar speed.
As I said no proper test, no timer, just looking at progress info on status bar, but if there is a difference, it not impressive - because it is anyway array formula by nature.