Lookup more than less than

[sahil]

Hello Folks,

I tried multiple functions but none did work for me and I need your help for the same.

I have one column (C) and I want to know row number if the value is searched more than and in another cell less than is found.

for example in D4 result should be 9 and in E4 result should be 17.

Appreciate your help.

Analysis.xlsx
	C	D	E
2	Index	Finding row if less than	Finding row if more than
3	10	7	65
4	27	??	??
5	37
6	48
7	37
8	12
9	3
10	56
11	7
12	45
13	34
14	24
15	3
16	5
17	67
18	45
19	32
20	3
21	4
22	24
23	42
24	34
Sheet1

 

[earthworm]

Hello Folks,

I tried multiple functions but none did work for me and I need your help for the same.

I have one column (C) and I want to know row number if the value is searched more than and in another cell less than is found.

for example in D4 result should be 9 and in E4 result should be 17.

Appreciate your help.

Analysis.xlsx
	C	D	E
2	Index	Finding row if less than	Finding row if more than
3	10	7	65
4	27	??	??
5	37
6	48
7	37
8	12
9	3
10	56
11	7
12	45
13	34
14	24
15	3
16	5
17	67
18	45
19	32
20	3
21	4
22	24
23	42
24	34
Sheet1

Do you want the result for all possible combination ?

 

[sahil]

Hi Earthworm,

I am looking for first value found, its row number as a return from by function

If first value found less than, then its row number in D4 (reference value if D3)
If first value found more than, then its row number in E4 (reference value is E3)

(First value is a search task from top to bottom of C column)

 

[bebo021999]

D4 should be:

=MATCH(TRUE,INDEX($C$3:$C$24<D3,),0)+2

Copy to E4, change "<" to ">".

 

[sahil]

D4 should be:

=MATCH(TRUE,INDEX($C$3:$C$24<D3,),0)+2

Copy to E4, change "<" to ">".

It worked, thanks for that