Finding closest value in a non sorted column (equal or greater)

[cidfidou]

Hi Excel Masters,

i am trying to come up with a formula that can give me the closest value in a non sorted column (equal or greater). I can not use a simple match as my column can not be sorted.. I am using the below formula but doesnt work all the time...

Any help would be greatly appreciated!!!

SLrange = my rage
AJ20 the lookup value

=INDEX(SLrange,MATCH(MIN(ABS(SLrange-AJ20)),ABS(SLrange-AJ20),0))

 

[exceldevs]

Hi cidfidou,

This should be an array formula, try press F2 on the cell and press Ctrl+Shift+Enter (you'll see curly brackets created for array formula)

 

[cidfidou]

Hi Exceldevs,

thanks for answer... I forgot to put the curly bracket in my post but not in my excel file... below an is an example of what I am trying to achieve

[TABLE="width: 226"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Range[/TD]
[TD][/TD]
[TD]Lookup Value[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.8[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[exceldevs]

Could you share how you get the result for the 2's and 4?

 

[XOR LX]

Interesting. I would've thought there'd be a more concise solution than this, though I can't come up with it if there is. Again, array-entered:

=INDEX(SLrange,MATCH(MIN(IF(SLrange-AJ20>0,SLrange-AJ20)),SLrange-AJ20,0))

Regards

 

[Marcelo Branco]

Or


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

Range​

[/td][td] [/td][td]

Lookup Value​

[/td][td]

Result​

[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td] [/td][td]

0,3​

[/td][td]

1​

[/td][/tr]
[tr][td]

3​

[/td][td]

7​

[/td][td] [/td][td]

0,4​

[/td][td]

1​

[/td][/tr]
[tr][td]

4​

[/td][td]

6​

[/td][td] [/td][td]

0,8​

[/td][td]

1​

[/td][/tr]
[tr][td]

5​

[/td][td]

3​

[/td][td] [/td][td]

1​

[/td][td]

2​

[/td][/tr]
[tr][td]

6​

[/td][td]

2​

[/td][td] [/td][td]

1,1​

[/td][td]

2​

[/td][/tr]
[tr][td]

7​

[/td][td]

4​

[/td][td] [/td][td]

1,5​

[/td][td]

2​

[/td][/tr]
[tr][td]

8​

[/td][td]

5​

[/td][td] [/td][td]

3​

[/td][td]

4​

[/td][/tr]
[tr][td]

9​

[/td][td] [/td][td] [/td][td]

5,8​

[/td][td]

6​

[/td][/tr]
[tr][td]

10​

[/td][td] [/td][td] [/td][td]

9​

[/td][td]

Not Found​

[/td][/tr]
[/table]


Array formula in D2 copied down
=IFERROR(SMALL(IF($A$2:$A$8>C2,$A$2:$A$8),1),"Not Found")

confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Marcelo Branco]

REMARK: in the thread title you wrote equal or greater, but the expected results in #3 consider only values greater than.

Could you clarify?

M.

 

[cidfidou]

Thank you so much Marcelo.. Amazing Formula...

U r right... I made a mistake in my table in #3..
it should have been as below and works perfectly if we had a "=" ={IFERROR(SMALL(IF($A$2:$A$8>=C2,$A$2:$A$8),1),"Not Found")}

[TABLE="width: 256"]
<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Range[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Lookup Value[/TD]
[TD="class: xl65, width: 64"]Result[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]0.3[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]7[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]0.4[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]6[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]0.8[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]3[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]1.1[/TD]
[TD="class: xl66, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]4[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]1.5[/TD]
[TD="class: xl66, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]5[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]3[/TD]
[TD="class: xl66, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]5.8[/TD]
[TD="class: xl66, width: 64"]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[cidfidou]

Hi Xor,

thanks for your help

 

[Marcelo Branco]

Thank you so much Marcelo.. Amazing Formula...

You are welcome and thanks for the feedback.

M.