Check if Number Exist in Row Range

[ibmy]

Hi,

Sample of 100+k rows data:

3.1.xlsb
	D	E	F	G	H	I	J	K	L	M
1	Max		B		IB					expected result :
2
3	1.20702		1.20702		4					Max Exist
4	1.20702		1.20675
5	1.20702		1.20889
6	1.20702		1.20302
7	1.20702		1.20702
8	1.20703		1.20703		1					Max X
9	1.20703		1.20701
10	1.20706		1.20706		3					Max X
11	1.20706		1.20702
12	1.20706		1.20705
13	1.20706		1.20702
14	1.20702		1.20699
15	1.20724		1.20724		6					Max Exist
16	1.20724		1.20724
17	1.20724		1.20703
18	1.20706		1.20706
19	1.20706		1.20703
20	1.20707		1.20707
21	1.20707		1.20707
22	1.20707		1.20703
23	1.20707		1.20707
24	1.20707		1.20703		1					Max Exist
25	1.20710		1.20707
26	1.20712		1.20712
Sheet4

Data start at row 3.

Range = Start Range : Row x+1 -> End Range : Row x + Value in Column H
Example in Row 3:
H3 value is 4 so check number in D3 (1.20702) in F4:F7 (Start Range : Row 3+1=4 -> End Range : Row 3+4=7)
Result in Column M, Row 3, M3 "Max Exist" (1.20702 exist in F7). If number do not exist, result is "Max X"

 

[awoohaw]

Thanks all for the formula.

@Akuini , the code produce result all "Max X"

@ibmy , did the other suggestions work for you?

 

[Akuini]

@Akuini , the code produce result all "Max X"

It works on my side:

Book1
	D	E	F	G	H	I	J	K	L	M
1	Max		B		IB
2
3	120.702		120.702		4					Max Exist
4	120.702		120.675
5	120.702		120.889
6	120.702		120.302
7	120.702		120.702
8	120.703		120.703		1					Max X
9	120.703		120.701
10	120.706		120.706		3					Max X
11	120.706		120.702
12	120.706		120.705
13	120.706		120.702
14	120.702		120.699
15	120.724		120.724		6					Max Exist
16	120.724		120.724
17	120.724		120.703
18	120.706		120.706
19	120.706		120.703
20	120.707		120.707
21	120.707		120.707
22	120.707		120.703
23	120.707		120.707
24	120.707		120.703		1					Max Exist
25	12.071		120.707
26	120.712		120.712
Sheet1

 

[ibmy]

@ibmy , did the other suggestions work for you?

@awoohaw It works, thanks all of you.