Index Match betwen two tables with 2nd criteria

Does the entire activity have to take place within the alloted cycle period, or do they merely have to overlap?
For example: activity 9690.1 starts before cycle 11, but ends within it.

So we just assume any activity that started before the end of cycle 10 is part of cycle 10, since there's no cycle 9,8,7... cutoffs? That seems pretty loose. Extending the post#3 diagram, Activity 0 is assumed to be cycle 10, even if it ended before cycle 10 began. And Activity 4 is ignored completely? Or assumed to be cycle 12?

OK, paste this ARRAY FORMULA* in E2 and copy down:

Formula:

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E	F	G	H	I
1	Location	Activity	Start	End	Cycle		Location	Cycle	End
2	9690	1	7/25/17 10:00	7/25/17 13:30	10		9690	10	7/25/17 13:00
3	9465	1	7/25/17 10:00	7/25/17 12:00	8		9690	11	7/25/17 19:00
4	9690	2	7/25/17 13:30	7/25/17 18:00	11		9465	8	7/25/17 11:00
5	9690	3	7/25/17 18:00	7/26/17 1:00	11		9465	9	7/25/17 18:00
6	9465	2	7/25/17 12:00	7/25/17 19:00	9

NOTE: This returns the first (lowest-numbered) cycle for stated location which ends AFTER the activity starts.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

The MIN function returns the smallest NUMERIC value from an array, while ignoring BOOLEAN (true/false) values.
So the IF clause returns a cycle NUMBER if TRUE, else it returns FALSE (because no value was specified for that part).
Taking cell E2 as an example:

=MIN(IF(($I$2:$I$5>C2)*($G$2:$G$5=A2),$H$2:$H$5))

=MIN(IF({TRUE;TRUE;TRUE;TRUE}*($G$2:$G$5=A2),$H$2:$H$5))

=MIN(IF({TRUE;TRUE;TRUE;TRUE}*{TRUE;TRUE;FALSE;FALSE},$H$2:$H$5))

=MIN(IF({1;1;0;0},$H$2:$H$5))

=MIN(IF({TRUE;TRUE;FALSE;FALSE},{10;11;8;9}))

=MIN({10;11;FALSE;FALSE})

=10

NOTE- TRUE=1, FALSE=0 if used in math. When multiplying arrays, corresponding values are multiplied, yielding a results array of the same size.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee