# Find value closest to another

#### alanpack

##### New Member
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.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

##### MrExcel MVP
 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:
Rich (BB code):
``````
=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

##### Well-known Member
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

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

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

You are welcome.

Replies
1
Views
483
Replies
1
Views
479
Replies
2
Views
310
Replies
4
Views
344
Replies
11
Views
418

1,195,629
Messages
6,010,776
Members
441,569
Latest member
PeggyLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back