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
[TABLE="width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]20
[/TD]
[TD]9
[/TD]
[TD]25
[/TD]
[TD]7
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]95
[/TD]
[TD]55
[/TD]
[TD][/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]



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
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]9[/TD]
[TD]25[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]95[/TD]
[TD]55[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]55[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]95[/TD]

</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

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ffff00, align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]L2[/TH]
[TD="align: left"]{=IFERROR(LARGE(IF($A2:$J2 < $L$1,$A2:$J2,""),COLUMNS($L$1:L$1)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]M2[/TH]
[TD="align: left"]{=IFERROR(LARGE(IF($A2:$J2 < $L$1,$A2:$J2,""),COLUMNS($L$1:M$1)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]N2[/TH]
[TD="align: left"]{=IFERROR(LARGE(IF($A2:$J2 < $L$1,$A2:$J2,""),COLUMNS($L$1:N$1)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]O2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A2:$J2>$L$1,$A2:$J2,""),COLUMNS($O$1:O$1)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]P2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A2:$J2>$L$1,$A2:$J2,""),COLUMNS($O$1:P$1)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]Q2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A2:$J2>$L$1,$A2:$J2,""),COLUMNS($O$1:Q$1)),"")}[/TD]
[/TR]
</TBODY>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</TBODY>[/TABLE]



Hope that helps.