Formula to check ID and timestamp against list

[blacklotus0014]

I have a tab with about 10,000 records with IDs and timestamps (date and time). On another tab I have a list of IDs and start and end timestamps. There are duplicate IDs on both tabs. I want a formula for the 10,000 records tab that checks the ID and timestamp for each row against all the IDs and start/end time stamps of the other column and flags the rows where the ID and timestamp fall within the start/end timestamp on the other tab. It seems pretty simple, and it feels like the code should be something like this (but this code doesn't work):

=IF(AND(F2=List!C:C,B2>=List!F:F,B2<=List!G:G),"Y","N")

where F2 is the ID and B2 is the timestamp on the records tab. The "List" tab has the ID in column C and the start/end timestamps in columns F and G respectively. Any help would be greatly appreciated!

[Jacc]

A standard COUNTIFS is quick and easy:

Please Login or Register  to view this content.

This is what you were after but it's an array formula, has to be entered with Ctrl + Shift + Enter. If you feel like testing it DON'T use full columns, it will take forever. Already 10000 rows will be slow.

Please Login or Register  to view this content.