closet 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.
o/s ch
16.337
-2.318
-5.013 34.352
2.158
12.358
11.514
-22.643
-1.186
17.193
-18.996
27.852 110.781
-31.614 127.861
25.418


I want 34.352 to be displayed

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(B1:B13, MATCH(MIN(IF(B1:B13<>"", ABS(A1:A13))), ABS(A1:A13),
FALSE))

 

[dhstein]

JE McGimpsey,

That's terrific. I was reading the post and had no idea how to do that.
I tried your solution and it works. But I have no idea what it's doing or
why it works. Any chance you could explain it a little bit? Thanks.

 

[JE McGimpsey]

Briefly:

x: IF(B1:B13<>"", ABS(A1:A13)

returns an array of the absolute values for each cell in A1:A13 for
which there's a corresponding value in B1:B13. If there's no
corresponding B value, the array entry contains FALSE.

y: MIN(x)

returns the minimum value in the array of absolute values.

z: MATCH(y, ABS(A1:A13), FALSE)

returns the index of the minimum value within the range of absolute
values

and INDEX(B1:B13, z)

returns the value in B1:B13 corresponding to that index.

 

[dhstein]

Thanks JE.

Briefly:

x: IF(B1:B13<>"", ABS(A1:A13)

returns an array of the absolute values for each cell in A1:A13 for
which there's a corresponding value in B1:B13. If there's no
corresponding B value, the array entry contains FALSE.

y: MIN(x)

returns the minimum value in the array of absolute values.

z: MATCH(y, ABS(A1:A13), FALSE)

returns the index of the minimum value within the range of absolute
values

and INDEX(B1:B13, z)

returns the value in B1:B13 corresponding to that index.