Ruturn values from Table from time range

[mole999]

I need to return Start and Start2 separately, when a time supplied is between length and length2. Currently 5 IFs but need to extend to 13 periods

length	length2	Start	Start2	test
05:59:59	0	0	0	0
06:00:00	06:29:59	02:00:00	04:00:00	1		07:29:00
06:30:00	06:59:59	02:30:00	04:30:00	2
07:00:00	07:29:59	03:00:00	05:00:00	3
07:30:00	07:59:59	03:30:00	05:30:00	4
08:00:00	08:29:59	02:30:00	04:30:00	5
08:30:00	08:59:59	03:00:00	06:00:00	6
09:00:00	09:29:59	03:30:00	06:30:00	7
09:30:00	09:59:59	04:00:00	07:00:00	8
10:00:00	10:29:59	04:00:00	07:00:00	9
10:30:00	10:59:59	04:30:00	07:30:00	10
11:00:00	11:29:59	04:30:00	07:30:00	11
11:30:00	11:59:59	04:30:00	07:30:00	12
12:00:00	12:29:59	04:30:00	07:30:00	13

<tbody>
</tbody>

 

[steve the fish]

These two should work:

=LOOKUP(G3,A2:A15,C2:C15)
=LOOKUP(G3,A2:A15,D2:D15)

I presume the first length and length2 are mixed up?

 

[mole999]

These two should work:

=LOOKUP(G3,A2:A15,C2:C15)
=LOOKUP(G3,A2:A15,D2:D15)

I presume the first length and length2 are mixed up?

I was way off with MATCH OR. Times should be right for thirty minute intervals and the 8 oclock is as intended to be

 

[steve the fish]

I meant the 0 should be in cell A2 and the 5:59:59 in cell B2.

 

[steve the fish]

You should be aware the lookup method has no upper bound limit after 12:00 based on your current table so if you arent completing all half hour intervals then it could produce erroneous results such as if you tested 13:00:00. This time isnt available in your table but would produce a result. If that matters you could use:

=INDEX(C2:C15,MATCH(1,INDEX((A2:A15<=G3)*(B2:B15>=G3),0),0))

 

[mole999]

maximum is a 12 hour planned shift, the first bit appears to bean error in my play table, and nothing untoward or special