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

 

[Cubist]

Hi,
Why is the result for Steve 3? His target low is 1 and it is first matched in row D2.

 

[juken1]

oh yes your right my bad lol , Steve result should be 1

 

[Cubist]

Which version of XL are you using?

 

[juken1]

using office365

 

[Cubist]

You should update your Account Details. Try this. The result returns the columns' order that matches your data but can be manipulated into the order you want if needed.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Date	Time	Number	Name	Target High	Target Low	Target Date	Target Time	Target Reached
2	Steve	1/1/24	0.41666667	1	Steve	2.9	1	1/1/24	0.416667	1
3	Steve	1/1/24	0.5625	2	John	32	24.8	1/1/24	0.5625	23
4	Steve	1/1/24	0.58333333	1
5	Steve	1/2/24	0.41666667	3
6	Steve	1/2/24	0.5625	4
7	Steve	1/2/24	0.58333333	5
8	Steve	1/3/24	0.41666667	3
9	Steve	1/3/24	0.5625	2
10	Steve	1/3/24	0.58333333	1
11	John	1/1/24	0.41666667	30
12	John	1/1/24	0.5625	23
13	John	1/1/24	0.58333333	27
14	John	1/2/24	0.41666667	22
15	John	1/2/24	0.5625	16
16	John	1/2/24	0.58333333	19
17	John	1/3/24	0.41666667	22
18	John	1/3/24	0.5625	21
19	John	1/3/24	0.58333333	27
Sheet4

Cell Formulas
Range	Formula
H2:J3	H2	=XLOOKUP(1,($A$2:$A$19=E2)*(($D$2:$D$19>=F2)+($D$2:$D$19<=G2)),$B$2:$D$19)
Dynamic array formulas.

 

[juken1]

Thats look good thanks, is it possible to have the formula seraching after a specific time.

I've added column H as a time
So K column result for Steve should be 1 as i need the formula to look after 10:05AM for Steve

And for John result should be 33 as I need to look after 1:50 PM

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Date	Time	Number	Name	Target High	Target Low	After Time	Target Date	Target Time	Target Reached
2	Steve	45292	10:00:00 AM	3	Steve	3	1	10:05 AM	Monday, 1 January 2024	10:00:00 AM	3
3	Steve	45292	1:30:00 PM	1	John	32	25	1:50 PM	Monday, 1 January 2024	10:00:00 AM	4
4	Steve	45292	2:00:00 PM	1
5	Steve	45293	10:00:00 AM	1
6	Steve	45293	1:30:00 PM	1
7	Steve	45293	2:00:00 PM	1
8	Steve	45294	10:00:00 AM	1
9	Steve	45294	1:30:00 PM	1
10	Steve	45294	2:00:00 PM	1
11	John	45292	10:00:00 AM	4
12	John	45292	1:30:00 PM	5
13	John	45292	2:00:00 PM	33
14	John	45293	10:00:00 AM	36
15	John	45293	1:30:00 PM	4
16	John	45293	2:00:00 PM	4
17	John	45294	10:00:00 AM	3
18	John	45294	1:30:00 PM	4
19	John	45294	2:00:00 PM	1
Sheet1

Cell Formulas
Range	Formula
I2:K3	I2	=XLOOKUP(1,($A$2:$A$19=E2)*(($D$2:$D$19>=F2)+($D$2:$D$19<=G2)),$B$2:$D$19)
Dynamic array formulas.

 

[Cubist]

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

Cell Formulas
Range	Formula
I2:K3	I2	=XLOOKUP(1,($C$2:$C$19>=H2)*($A$2:$A$19=E2)*(($D$2:$D$19>=F2)+($D$2:$D$19<=G2)),$B$2:$D$19)
Dynamic array formulas.

 

[juken1]

awesome thanks, does this formula support moving date column B? i moved date column to column L as a test but seems to have broken formula

 

[Cubist]

You would use HTACK. In the example, I've moved the date to F.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name		Time	Number		Date	Name	Target High	Target Low	After Time	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:30:00 PM	1
3	Steve		1:30:00 PM	1		1/1/24	John	32	25	1:50:00 PM	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
K2:M3	K2	=XLOOKUP(1,($C$2:$C$19>=J2)*($A$2:$A$19=G2)*(($D$2:$D$19>=H2)+($D$2:$D$19<=I2)),HSTACK($F$2:$F$19,$C$2:$D$19))
Dynamic array formulas.