Find number closest to 0

[snappertime]

I want to find the number in a column that is closest to 0 (number may be
positive or negative) and return a number in an adjacent cell, if no number
in the adjacent cell I want the next number that is closest to 0 that does
have a number in the adjacent cell.
eg.
offset chainage
-88.972
-2.174
22.693 215.459
24.099 231.342
93.596 355.034

I want 215.459 to be returned.

 

[Ron Rosenfeld]

I want to find the number in a column that is closest to 0 (number may be
positive or negative) and return a number in an adjacent cell, if no number
in the adjacent cell I want the next number that is closest to 0 that does
have a number in the adjacent cell.
eg.
offset chainage
-88.972
-2.174
22.693 215.459
24.099 231.342
93.596 355.034

I want 215.459 to be returned.

Enter this as an **array** formula:

=INDEX(chainage,MATCH(MIN(IF(ISNUMBER(chainage),offset)),offset,0))

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
If you do it correctly, Excel will place braces {...} around the formula.
--ron

 

[snappertime]

Thanks mate that works

Enter this as an **array** formula:

=INDEX(chainage,MATCH(MIN(IF(ISNUMBER(chainage),offset)),offset,0))

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
If you do it correctly, Excel will place braces {...} around the formula.
--ron

 

[Ron Rosenfeld]

Thanks mate that works

You're welcome. Glad to help.
Thanks for the feedback.
--ron