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

Range		Lookup Value	Result
1		0.3	1
7		0.4	1
6		0.8	1
3		1	2
2		1.1	2
4		1.5	2
5		3	4
		5.8	6

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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

	A​	B​	C​	D​
1​	Range​		Lookup Value​	Result​
2​	1​		0,3​	1​
3​	7​		0,4​	1​
4​	6​		0,8​	1​
5​	3​		1​	2​
6​	2​		1,1​	2​
7​	4​		1,5​	2​
8​	5​		3​	4​
9​			5,8​	6​
10​			9​	Not Found​

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")}

Range		Lookup Value	Result
1		0.3	1
7		0.4	1
6		0.8	1
3		1	1
2		1.1	2
4		1.5	2
5		3	3
		5.8	6

<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

 

[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.