Get Nearest value from a range based on a reference cell value

[hsandeep]

My Data Range is B2:G10 having 4 columns B,C,D & E. These 4 Columns are ‘continuous’ & have values which are either ‘INCREASING’ from B to E or ‘DECREASING’ from B to E.

Reference range is from G2:G10

Output required:
1. I2=’Nearest’ value from B to E with reference to G
2. I3=’First Left side’ value to the ’Nearest’ value found in I2. If there is NO ‘’First Left side’ value then I3=I2

How to accomplish?
Thanks in advance.
I am using Excel 2007.

Sheet1 Increasing

	B	C	D	E	F	G	H	I	J

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]44.00[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]55.68[/TD]
[TD="align: right"]58.00[/TD]

[TD="align: right"]55.70[/TD]

[TD="bgcolor: #ffff00, align: right"]55.68[/TD]
[TD="bgcolor: #ffcc00, align: right"]50.00[/TD]

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

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

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

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

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

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]33.00[/TD]
[TD="align: right"]88.00[/TD]
[TD="align: right"]99.00[/TD]
[TD="align: right"]100.00[/TD]

[TD="align: right"]100.00[/TD]

[TD="bgcolor: #ffff00, align: right"]100.00[/TD]
[TD="bgcolor: #ffcc00, align: right"]99.00[/TD]

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

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]33.00[/TD]
[TD="align: right"]88.00[/TD]
[TD="align: right"]99.00[/TD]
[TD="align: right"]100.00[/TD]

[TD="align: right"]40.00[/TD]

[TD="bgcolor: #ffff00, align: right"]33.00[/TD]
[TD="bgcolor: #ffcc00, align: right"]33.00[/TD]

</tbody>

Sheet 2 Decreasing

	B	C	D	E	F	G	H	I	J
500	400	300	100				100	300

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]101.00[/TD]
[TD="align: right"]88.00[/TD]
[TD="align: right"]75.00[/TD]
[TD="align: right"]58.00[/TD]

[TD="align: right"]75.05[/TD]

[TD="bgcolor: #ffff00, align: right"]75.00[/TD]
[TD="bgcolor: #ffcc00, align: right"]88.00[/TD]

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

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

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

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

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

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

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

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"]400.00[/TD]
[TD="align: right"]300.00[/TD]
[TD="align: right"]100[/TD]

</tbody>

 

[FormR]

Hi, here is one possible option you can try:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Asc
2	44.00	50.00	55.68	58.00		55.70		55.68	50.00
3	33.00	88.00	99.00	100.00		100.00		100.00	99.00
4	33.00	88.00	99.00	100.00		40.00		33.00	33.00
5
6	Desc
7	101.00	88.00	75.00	58.00		75.05		75.00	88.00
8	500.00	400.00	300.00	100.00		100.00		100.00	300.00
Sheet1

Cell Formulas
Range	Formula
I2	=IFERROR(LOOKUP(2,1/(A2:D2),A2:D2),H2)
I7	=IFERROR(LOOKUP(2,1/(A7:D7>H7),A7:D7),H7)
H2	{=INDEX(A2:D2,MATCH(MIN(ABS(A2:D2-F2)),ABS(A2:D2-F2),0))}
H7	{=INDEX(A7:D7,MATCH(MIN(ABS(A7:D7-F7)),ABS(A7:D7-F7),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DanteAmor]

Another options

Sheet1
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:111.21px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:62.73px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Increasing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">44.00</td><td style="text-align:right; ">50.00</td><td style="text-align:right; ">55.68</td><td style="text-align:right; ">58.00</td><td > </td><td style="text-align:right; ">55.70</td><td > </td><td style="text-align:right; ">55.68</td><td style="text-align:right; ">50.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="text-align:right; ">33.00</td><td style="text-align:right; ">88.00</td><td style="text-align:right; ">99.00</td><td style="text-align:right; ">100.00</td><td > </td><td style="text-align:right; ">100.00</td><td > </td><td style="text-align:right; ">100</td><td style="text-align:right; ">99.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="text-align:right; ">33.00</td><td style="text-align:right; ">88.00</td><td style="text-align:right; ">99.00</td><td style="text-align:right; ">100.00</td><td > </td><td style="text-align:right; ">40.00</td><td > </td><td style="text-align:right; ">33</td><td style="text-align:right; ">33.00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I2</td><td >=IFERROR(LOOKUP(G2,B2:E2),"")</td></tr><tr><td >J2</td><td >=IFERROR(INDEX(B2:E2,,IF(MATCH(G2,B2:E2,1)-1=0,1,MATCH(G2,B2:E2,1)-1)),"")</td></tr></table></td></tr></table>


Sheet2
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:109.31px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /><col style="width:44.67px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Decreasing</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">101</td><td style="text-align:right; ">88</td><td style="text-align:right; ">75</td><td style="text-align:right; ">58</td><td > </td><td style="text-align:right; ">75.05</td><td > </td><td style="text-align:right; ">75</td><td style="text-align:right; ">88</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">500</td><td style="text-align:right; ">400</td><td style="text-align:right; ">300</td><td style="text-align:right; ">100</td><td > </td><td > </td><td > </td><td style="text-align:right; ">100</td><td style="text-align:right; ">300</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="text-align:right; ">500</td><td style="text-align:right; ">400</td><td style="text-align:right; ">300</td><td style="text-align:right; ">100</td><td > </td><td > </td><td > </td><td style="text-align:right; ">100</td><td style="text-align:right; ">300</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I2</td><td >=IFERROR(INDEX(B2:E2,,SUMPRODUCT(MAX((B2:E2>=IF(G2<MIN(B2:E2),E2+1,G2))*COLUMN(B2:E2)))),"")</td></tr><tr><td >J2</td><td >=IFERROR(INDEX(B2:E2,,SUMPRODUCT(MAX((B2:E2>=IF(G2<MIN(B2:E2),E2+1,G2))*COLUMN(B2:E2)-1))),"")</td></tr></table></td></tr></table>

 

[hsandeep]

I think there ‘could’ be some communication error from my side:
There is NO sheet1 & Sheet2 i.e. B2:E10 is in 1 Worksheet ONLY
It is:
EITHER B>C>D>E
OR B<C<D<E
ALWAYS.
So there needs to be 1 formula only for “I” column & 1 formula only for “J” column taking care of INCREASING values or DECREASING values in their respective rows of B:E

 

[FormR]

<c<d<e
So there needs to be 1 formula only for “I” column & 1 formula only for “J” column taking care of INCREASING values or DECREASING values in their respective rows of B:E

Hi, in that case, here is an option you can try.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1
2	44	50	55.68	58		55.7		55.68	50
3	33	88	99	100		100		100	99
4	33	88	99	100		40		33	33
5	101	88	75	58		75.05		75	88
6	500	400	300	100		100		100	300
Sheet1

Cell Formulas
Range	Formula
I2	=IFERROR(IF(A2>D2,LOOKUP(2,1/(A2:D2>H2),A2:D2),LOOKUP(2,1/(A2:D2),A2:D2)),H2)
H2	{=INDEX(A2:D2,MATCH(MIN(ABS(A2:D2-F2)),ABS(A2:D2-F2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

</c<d<e

 

[hsandeep]

Hi, in that case, here is an option you can try.

Thanks FormR. THE FORMULA WORKS FINE.