Find value closest to another

[alanpack]

Hello, I am trying to find the value in a column that is closest to another value. The value I am comparing to can be negative so I don't want to exclude zero, I just want to exclude empty cells. Some formulas I've been trying return 0 as the closest value and I'm guessing it's because I have some empty cells in the column. There are no negative values in my column.

Thanks in advance.

 

[Aladin Akyurek]

Row\Col	A​	B​	C​
1​			closest to:
2​	2​		2.5​
3​	3​		2​
4​
5​	0​
6​	-4​
7​	4​
8​

C2, control+shift+enter, not just enter:

=INDEX(A2:A7,MATCH(MIN(ABS(IF(ISNUMBER(A2:A7),A2:A7,
    9.99999999999999E+307)-C2)),ABS(IF(ISNUMBER(A2:A7),
    A2:A7,9.99999999999999E+307)-C2),0))

You might want to replace the big number with its definition:

BigNum defined as referring to:

=9.99999999999999E+307

Formulas | Name Manager.

 

[XOR LX]

If you take the IF clause outside of the ABS construction, then no such reference to BigNum is necessary:

=INDEX(A2:A7,MATCH(MIN(IF(ISNUMBER(A2:A7),ABS(A2:A7-C2))),IF(ISNUMBER(A2:A7),ABS(A2:A7-C2)),0))

Regards

 

[alanpack]

Wow, that was fast. Thank you very much to both of you. That worked!

 

[Aladin Akyurek]

Wow, that was fast. Thank you very much to both of you. That worked!

You are welcome.