FIND next or higher next value in a table range

[sanyo1234]

hi all


this is my peculiar problem
please help me out


i have to lookup for TWO values and return the equal value or next higher value in the table

IF MY VALUES ARE 3 AND 450 the return value should be 500


ALSO MY VALUES ARE 7 AND 800 the return value should be 800


plasse help me put


thank you all

Table may look like as below




[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]410[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]900[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
4	1	10	100	200	300	400
5	2	20	200	300	400	500
6	3	30	300	400	500	600
7	4	40	400	500	600	700
8	5	50	500	600	700	800
9	6	60	600	700	800	900
10	7	70	700	800	900	1000
Sheet1

In B3 control+shift+enter, not just enter, and copy across:

=MIN(IF($A$4:$A$10=B1,IF($B$4:$F$10>=B2,$B$4:$F$10)))

 

[sanyo1234]

Superb sir

Made it very simple thank you very much sir

please tell me is there any way out other than array formula

 

[admiral100]

Hi,

Version without array Formula :

B3 =IFERROR(SMALL(INDIRECT("B"&B1+3):INDIRECT("F"&B1+3),COUNT(INDIRECT("B"&B1+3):INDIRECT("F"&B1+3))-COUNTIF(INDIRECT("B"&B1+3):INDIRECT("F"&B1+3),">="&B2)+1),0)


[TABLE="width: 490"]
<colgroup><col width="70" span="7" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[TD="class: xl63, width: 70"]D[/TD]
[TD="class: xl63, width: 70"]E[/TD]
[TD="class: xl63, width: 70"]F[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]410[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64"]500[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]100[/TD]
[TD="class: xl63"]200[/TD]
[TD="class: xl63"]300[/TD]
[TD="class: xl63"]400[/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]20[/TD]
[TD="class: xl63"]200[/TD]
[TD="class: xl63"]300[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]500[/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]30[/TD]
[TD="class: xl63"]300[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]500[/TD]
[TD="class: xl63"]600[/TD]
[/TR]
[TR]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]40[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]500[/TD]
[TD="class: xl63"]600[/TD]
[TD="class: xl63"]700[/TD]
[/TR]
[TR]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]50[/TD]
[TD="class: xl63"]500[/TD]
[TD="class: xl63"]600[/TD]
[TD="class: xl63"]700[/TD]
[TD="class: xl63"]800[/TD]
[/TR]
[TR]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]60[/TD]
[TD="class: xl63"]600[/TD]
[TD="class: xl63"]700[/TD]
[TD="class: xl63"]800[/TD]
[TD="class: xl63"]900[/TD]
[/TR]
[TR]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]70[/TD]
[TD="class: xl63"]700[/TD]
[TD="class: xl63"]800[/TD]
[TD="class: xl63"]900[/TD]
[TD="class: xl63"]1000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Superb sir

Made it very simple thank you very much sir

please tell me is there any way out other than array formula

1. The suggested formula, i.e.

=MIN(IF($A$4:$A$10=B1,IF($B$4:$F$10>=B2,$B$4:$F$10)))

is an array-processing formula that requires control+shift+enter in order to signal this fact to Excel.

2. The following...

=IFERROR(INDEX($B$4:$F$10,MATCH(B1,$A$4:$A$10,1),MATCH(B2,INDEX($B$4:$F$10,MATCH(B1,$A$4:$A$10,1),0),1)+(LOOKUP(B2,INDEX($B$4:$F$10,MATCH(B1,$A$4:$A$10,1),0))<B2)),MIN(INDEX($B$4:$F$10,MATCH(B1,$A$4:$A$10,1),0)))​

is a regular range-processing formula.

I'd think that [1] in this case is superior to [2].

​