INDEX MATCH BETWEEN TIMES?

[RedOctoberKnight]

Good Morning,

I'm looking to find out if its possible to use index and match using multiple criteria including time. One of the criteria would be whether or not the current time falls between two times and the other would be a door number. Trying to find out the truck that falls between the two times and specified door number. Below is a rough example.

Book2
	A	B	C	D	E	F
1	START	END	DOOR	TRUCK
2	11:00	12:10	1	A	Door	2
3	11:15	11:55	2	B	TIME	11:30
4	11:00	11:45	3	C	Truck
5	12:25	13:00	4	D
6	13:30	14:05	5	B
7	13:30	14:20	3	C
8	14:00	15:00	1
9	14:20	15:30	2
10	14:55	15:40	3
11	16:05	17:05	1
12	16:40	17:20	2
13	17:05	17:50	3
14	17:20	18:15	1
15	17:25	18:05	2
16	18:45	19:30	3
Sheet1

I would also like the time to be based off the current time if possible

Any help would be much appreciated. Thanks!

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F
1	START	END	DOOR	TRUCK
2	11:00	12:10	1	A	Door	2
3	11:15	11:55	2	B	TIME	11:30
4	11:00	11:45	3	C	Truck	B
5	12:25	13:00	4	D
6	13:30	14:05	5	B
7	13:30	14:20	3	C
8	14:00	15:00	1
9	14:20	15:30	2
10	14:55	15:40	3
11	16:05	17:05	1
12	16:40	17:20	2
13	17:05	17:50	3
14	17:20	18:15	1
15	17:25	18:05	2
16	18:45	19:30	3
Results

Cell Formulas
Range	Formula
F4	F4	=INDEX(D2:D16,AGGREGATE(15,6,(ROW(D2:D16)-ROW(D2)+1)/(C2:C16=F2)/(MOD(A2:A16,1)<=F3)/(MOD(B2:B16,1)>=F3),1))

 

[RedOctoberKnight]

Thanks Fluff. I modified the formula to fit my worksheet. I'm having issues with using the now( ) time. if I put the now( ) time in F2 and I change the times in the columns to where the now( ) time falls between them, I get a #num error. I'm assuming it has to do with now( ) time formatting. Is there any way to fix that?

 

[Fluff]

Now returns date & time, whereas your data showed F3 just had time, try

=INDEX(D2:D16,AGGREGATE(15,6,(ROW(D2:D16)-ROW(D2)+1)/(C2:C16=F2)/(MOD(A2:A16,1)<=MOD(F3,1))/(MOD(B2:B16,1)>=MOD(F3,1)),1))

 

[RedOctoberKnight]

Thanks Fluff! Works perfectly. I'm sure I'll have more questions to come. Appreciate the help!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[TMS123]

Glad to help & thanks for the feedback.

Hi Fluff,

I have a similar issue, want to do the same thing, but looking up a full column, if that doesn´t matter the only difference is that I have to look at time with the date: "2022-07-20 16:18". When I use your formula above, it seems to return an off value, maybe 100 rows below. Also the indexed value is not liked to the row criteria I put in?

Can you help me, please?

 

[Fluff]

Hi & welcome to MrExcel.
Please start a thread of your own for this question & explain exactly what you are trying do. Also posting some data will help potential helpers.

 

[TMS123]

Hi & welcome to MrExcel.
Please start a thread of your own for this question & explain exactly what you are trying do. Also posting some data will help potential helpers.

I did, thank you! it is called:
GANTT schedule - Take value if between time and equipment criteria match