Finding Numbers from a Column Using Target Numbers

[juken1]

Hello,
Could I please have some excel help.
I'm looking for a formula to go into column H, I and J

Column H Formula: The formula I need to go into column H is using target numbers from Column F and G, use those target numbers to search Column D from top to bottom and whichever number is matched first (or if the "Target Higher" is matched first the number can go over or "Target Low: matched number can go lower)

Coumn I and J should be the time and date for the matched number.

For example purposes I've manually entered the results into the grey section (column H, I and J) to display what should be there if I can get a formula working.

[

mrexcel.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Name	Date	Time	Number	Name	Target High	Target Low	Target Reached	Target Date	Target Time
2	Steve	1/01/2024	10:00:00 AM	1	Steve	2.9	1	3	2/01/2024	10:00:00 AM
3	Steve	1/01/2024	1:30:00 PM	2	John	32	24.8	23	1/01/2024	1:30:00 PM
4	Steve	1/01/2024	2:00:00 PM	1
5	Steve	2/01/2024	10:00:00 AM	3
6	Steve	2/01/2024	1:30:00 PM	4
7	Steve	2/01/2024	2:00:00 PM	5
8	Steve	3/01/2024	10:00:00 AM	3
9	Steve	3/01/2024	1:30:00 PM	2
10	Steve	3/01/2024	2:00:00 PM	1
11	John	1/01/2024	10:00:00 AM	30
12	John	1/01/2024	1:30:00 PM	23
13	John	1/01/2024	2:00:00 PM	27
14	John	2/01/2024	10:00:00 AM	22
15	John	2/01/2024	1:30:00 PM	16
16	John	2/01/2024	2:00:00 PM	19
17	John	3/01/2024	10:00:00 AM	22
18	John	3/01/2024	1:30:00 PM	21
19	John	3/01/2024	2:00:00 PM	27
Data

 

[juken1]

hey cheers for your help with this Cubist, just one last thing can the J column have a column next to it saying "After Date" so i can have a specific date and time to start the search after to find numbers

 

[Cubist]

Like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name		Time	Number		Date		Name	Target High	Target Low	After Time	After Date	Target Date	Target Time	Target Reached
2	Steve		10:00:00 AM	3		1/1/24		Steve	3	1	10:05:00 AM	1/1/24	1/1/24	1:30:00 PM	1
3	Steve		1:30:00 PM	1		1/1/24		John	32	25	1:50:00 PM	1/1/24	1/1/24	2:00:00 PM	33
4	Steve		2:00:00 PM	1		1/1/24
5	Steve		10:00:00 AM	1		1/2/24
6	Steve		1:30:00 PM	1		1/2/24
7	Steve		2:00:00 PM	1		1/2/24
8	Steve		10:00:00 AM	1		1/3/24
9	Steve		1:30:00 PM	1		1/3/24
10	Steve		2:00:00 PM	1		1/3/24
11	John		10:00:00 AM	4		1/1/24
12	John		1:30:00 PM	5		1/1/24
13	John		2:00:00 PM	33		1/1/24
14	John		10:00:00 AM	36		1/2/24
15	John		1:30:00 PM	4		1/2/24
16	John		2:00:00 PM	4		1/2/24
17	John		10:00:00 AM	3		1/3/24
18	John		1:30:00 PM	4		1/3/24
19	John		2:00:00 PM	1		1/3/24
Sheet5

Cell Formulas
Range	Formula
M2:O3	M2	=XLOOKUP(1,($F$2:$F$19+$C$2:$C$19>=L2+K2)*($A$2:$A$19=H2)*(($D$2:$D$19>=I2)+($D$2:$D$19<=J2)),HSTACK($F$2:$F$19,$C$2:$D$19))
Dynamic array formulas.