Some kind of array/vlookup combination with an IF and OR function

[BvN]

Hello,

I have got two sheets of data (see example file). Sheet 1 has the processing times of different processing steps. In Sheet 2 the occurred downtime's have been logged:

Sheet 1:
Column1: Name process step
Column2: Start time (Format: e.g. 11/3/2012 15:00)
Column3: End time (Format: e.g. 11/3/2012 17:30)

Sheet 2:
Column1: Downtime cause
Column2: Start time (Format: e.g. 11/3/2012 15:30)
Column3: End time (Format: e.g. 11/3/2012 15:40)

I would like to add a 4th column to Sheet1 which shows if downtime has occurred during the process step. In doing so I will be able to filter out the process steps where downtime have occurred.

So the function must show if the start and/or end time of the downtime lies between the starting time and end time of the process step.

I have been trying to get some kind of formula working, but just cannot figure its out.

Help will be much appreciated!

[ChemistB]

I used this to get the downtime cause

=IF(SUMPRODUCT((Downtime!$B$2:$B$24>=B2)*(Downtime!$C$2:$C$24<=C2)*(ROW($B$2:$B$24)-1))=0, "",INDEX(Downtime!$A$2:$A$24, SUMPRODUCT((Downtime!$B$2:$B$24>=B2)*(Downtime!$C$2:$C$24<=C2)*(ROW($B$2:$B$24)-1))))
See attachment.
Is that what you wanted?

[BvN]

I used this to get the downtime cause

=IF(SUMPRODUCT((Downtime!$B$2:$B$24>=B2)*(Downtime!$C$2:$C$24<=C2)*(ROW($B$2:$B$24)-1))=0, "",INDEX(Downtime!$A$2:$A$24, SUMPRODUCT((Downtime!$B$2:$B$24>=B2)*(Downtime!$C$2:$C$24<=C2)*(ROW($B$2:$B$24)-1))))
See attachment.
Is that what you wanted?

Thanks! That is what I wanted indeed. It took me while to get it working, but once I figured out that the arrays should be the same length it is working fine.

A side question: is it possible to stop a calculation once you started it? I did read that F9 works to start calculating when it is set on manual and shift+F9 for just the sheet. But could not find if there is a way to stop.

[ChemistB]

I'm not sure if Esc works or not. Sorry. Glad the formula is working for you though.