how can I pick up a nearest value to zero

[aiurelbn]

In a range of cells, I have negative and positive numbers. How can I obtain,
in another cell, the value from my range that is negative AND nearest to
zero, and, in another cell, the positive number AND nearest to zero ? Example
: in my range of cells, I have : -10 , -7 , -3 , 1 , 4 , 7 , 9. I need to
extract from this range values -3 and 1. The values in my range are changing
everytime, because they are provided by formulas.
Thank you !

 

[Max]

This might suffice ..
Assuming data as posted is in A1:A7, in ascending order (as posted)
in say, B1 and in C1
=INDEX(A1:A7,MATCH(0,A1:A7))
=INDEX(A1:A7,MATCH(0,A1:A7)+1)

 

[Teethless mama]

=MAX(IF(A1:A7<0,A1:A7))
=MIN(IF(A1:A7>0,A1:A7))

ctrl+shift+enter, not just enter

 

[Max]

The simpler, non-array index/match option would have worked fine too, if the
data is sorted in ascending order, as you had originally posted, and as
pointed out in the response. Do not dismiss the option as not helpful.

---