# Get the next possible larger value next to the current value

• Hi,

I have a data where I want to create a formula fulfilling the below conditions.

Suppose I have two fields: Table1(A) and Table2(B)
If a value ex.98 (A COLUMN) matches with the value with 98(B COLUMN),it should pick up my second higher value 103. suppose 103 is the next high value of 98 .

[TABLE="width: 903"]

[tr]

[td]

TABLE1

[/td]

[td]

TABLE2

[/td]

[td][/td]

[td]

Required Result

[/td]

[td]

Condition

[/td]

[td]

OUTPUT

[/td]

[/tr]

[tr]

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

[td][/td]

[td]

98 then 103

[/td]

[td]

if value of table1=98 then 103from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

103 then 149

[/td]

[td]

if value of table1=103 then 149from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

149 then 175

[/td]

[td]

if value of table1=149 then 175from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

175 then 198

[/td]

[td]

if value of table1=175 then 198from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

198 then 199

[/td]

[td]

if value of table1=198 then 199from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

199 then 201

[/td]

[td]

if value of table1=199 then 201from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

201 then 245

[/td]

[td]

if value of table1=201 then 245from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

245 then 251

[/td]

[td]

if value of table1=245 then 251from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

251 then 351

[/td]

[td]

if value of table1=251 then 351from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

351 then 352

[/td]

[td]

if value of table1=351 then 352from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

352 then 451

[/td]

[td]

if value of table1=352 then 451from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

451 then 499

[/td]

[td]

if value of table1=451 then 499from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

499 then 598

[/td]

[td]

if value of table1=499 then 598from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

598 then 599

[/td]

[td]

if value of table1=598 then 599from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

599 then 601

[/td]

[td]

if value of table1=599 then 601from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

601 then 751

[/td]

[td]

if value of table1=601 then 751from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

751 then 851

[/td]

[td]

if value of table1=751 then 851from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

851 then 949

[/td]

[td]

if value of table1=851 then 949from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

949 then 951

[/td]

[td]

if value of table1=949 then 951from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

951 then 1251

[/td]

[td]

if value of table1=951 then 1251from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

1251 then 1501

[/td]

[td]

if value of table1=1251 then 1501from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[tr]

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

[td][/td]

[td]

1501 then 1501

[/td]

[td]

if value of table1=1501 then 1501from table 2 (next large number)

[/td]

[td][/td]

[/tr]

[/TABLE]

## Files

• Re: Get the next possible larger value next to the current value

• Re: Get the next possible larger value next to the current value

In this case just use an if function in D2 and copy down.

=IF(A2=B2,B3,B2)

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: Get the next possible larger value next to the current value

• Re: Get the next possible larger value next to the current value

This is ok...But if the data is not sorted in ascending or descending order?
What shoild I do.

• Re: Get the next possible larger value next to the current value

=IF(A2=B2,SMALL(\$B\$2:\$B\$23,1+COUNTIF(\$B\$2:\$B\$23,"<="&B2)),B2)

@ StephenR

Actually he did it! :yourock:

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: Get the next possible larger value next to the current value

Nooooopp...

it is not working while the Fileld 2 data is not in order. Only working while data is in order.

## Files

• Re: Get the next possible larger value next to the current value

Quote

...If a value ex.98 (A COLUMN) matches with the value with 98(B COLUMN),it should pick up my second higher value 103

Formula does this.

IF A2=B2, then will give you the next higher value of B2. IF a2<>b2, then will gives B2 value.

In your last example no cell of column B matces with cell in column A. So the result is the value of column B.

Without any condition this formula in C2 and copy down, gives your expected results.

=SMALL(\$B\$2:\$B\$23,ROW(B2))

Edit: Or..

=IF(MATCH(A2,\$B\$2:\$B\$23,0),SMALL(\$A\$2:\$A\$23,1+COUNTIF(\$A\$2:\$A\$23,"<="&A2)),A2)

## Files

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

• Re: Get the next possible larger value next to the current value

misprepaid.asm: please don't treat people answering questions here as if they were your lackeys. And take the time to set out all the details of your question at the beginning - this avoids all concerned wasting time.

• Re: Get the next possible larger value next to the current value

Re: post #4

Allow a minimum of 18-24 hours to elapse before you bump your thread. This is a free forum staffed by volunteers generously giving of their time and talent.
Give adequate time for someone to take interest.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Get the next possible larger value next to the current value

Quote from StephenR;650189

misprepaid.asm: please don't treat people answering questions here as if they were your lackeys. And take the time to set out all the details of your question at the beginning - this avoids all concerned wasting time.

I apologize for my mistake..I will surely keep in mind from this thime onwards.

• Re: Get the next possible larger value next to the current value

Yes. keep in mind for the next time.... This time I was obliged (as a good servant) to do .. No need to thank me for it. it was just .. my job.:0ops:

From simple curiosity, what was ultimately the solution you wanted?

Regards

Fotis :hammerhe:

[SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

[SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

[SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!

[SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!