how to find 1st 2nd 3rd ......closest lower and higher value with refrence to specific cell

[anilmg1982]

DER MR EXCEL, HAPPY NEW YEAR 2013

pls find TABLE BELOW (MY ACTUAL DATA IS HUGE )

my data is horizontal data


L1 is the cell that i want to match with range a2:j2

my L1 VALUE IS =8

NOW I WANT FORMULA WHICH RETURNS NEAREST FIRST LOW VALUE TO L1(8),
WHICH IS F2(7),THEN IN NEXT CELL I WANT H2(5) WHICH IS NEXT LOWER VALUE NEXT TO 7,THEN IN NEXT CELL I WANT G2(3) WHICH IS NEXT LOWER VALUE NEXT TO 5

NOW I ALSO NEED FORMULA

I WANT FORMULA WHICH RETURNS NEAREST FIRST HIGH VALUE TO L1(8),
WHICH IS D2(9),THEN IN NEXT CELL I WANT A2(10) WHICH IS NEXT HIGHER VALUE NEXT TO 9,THEN IN NEXT CELL I WANT B2(15) WHICH IS NEXT HIGHER VALUE NEXT TO 10

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

10

15

20

9

25

7

3

5

95

55

8

<TBODY>
</TBODY>

PLS HELP ME OUT MASTER MR ECXEL

REGARDS ANIL
THANKS

 

[Robert Mika]

DER MR EXCEL, HAPPY NEW YEAR 2013

pls find TABLE BELOW (MY ACTUAL DATA IS HUGE )

my data is horizontal data


L1 is the cell that i want to match with range a2:j2

my L1 VALUE IS =8

NOW I WANT FORMULA WHICH RETURNS NEAREST FIRST LOW VALUE TO L1(8),
WHICH IS F2(7),THEN IN NEXT CELL I WANT H2(5) WHICH IS NEXT LOWER VALUE NEXT TO 7,THEN IN NEXT CELL I WANT G2(3) WHICH IS NEXT LOWER VALUE NEXT TO 5

NOW I ALSO NEED FORMULA

I WANT FORMULA WHICH RETURNS NEAREST FIRST HIGH VALUE TO L1(8),
WHICH IS D2(9),THEN IN NEXT CELL I WANT A2(10) WHICH IS NEXT HIGHER VALUE NEXT TO 9,THEN IN NEXT CELL I WANT B2(15) WHICH IS NEXT HIGHER VALUE NEXT TO 10

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

10

15

20

9

25

7

3

5

95

55

8

<tbody>
</tbody>

PLS HELP ME OUT MASTER MR ECXEL

REGARDS ANIL
THANKS

Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

10

15

20

9

25

7

3

5

95

55

*

8

7

5

3

*

*

*

*

*

*

2

*

*

*

*

*

*

*

*

*

*

*

8

9

10

15

20

25

55

95

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula M1 {=IFERROR(LARGE(IF($A$1:$J$1<l1,$a$1:$j$1)< span="">,1)</l1,$a$1:$j$1)<>,"")} M2 {=IFERROR(SMALL(IF($A$1:$J$1>L2,$A$1:$J$1),1),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
Drag accross.
Excel 07/10 version.

 

[Snakehips]

anilmg1982,

Perhaps....

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1												8
2	10	15	20	9	25	7	3	5	95	55		7	5	3	9	10	15

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Array Formulas Cell Formula L2 {=IFERROR(LARGE(IF($A2:$J2 < $L$1,$A2:$J2,""),COLUMNS($L$1:L$1)),"")} M2 {=IFERROR(LARGE(IF($A2:$J2 < $L$1,$A2:$J2,""),COLUMNS($L$1:M$1)),"")} N2 {=IFERROR(LARGE(IF($A2:$J2 < $L$1,$A2:$J2,""),COLUMNS($L$1:N$1)),"")} O2 {=IFERROR(SMALL(IF($A2:$J2>$L$1,$A2:$J2,""),COLUMNS($O$1:O$1)),"")} P2 {=IFERROR(SMALL(IF($A2:$J2>$L$1,$A2:$J2,""),COLUMNS($O$1:P$1)),"")} Q2 {=IFERROR(SMALL(IF($A2:$J2>$L$1,$A2:$J2,""),COLUMNS($O$1:Q$1)),"")} <THEAD> </THEAD><TBODY> </TBODY> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>

Hope that helps.