Finding the number second closest to zero

[gowseph]

Currently using: =INDEX(C7:C9,MATCH(MIN(IF(ISNUMBER(C7:C9),ABS(C7:C9-0))),IF(ISNUMBER(C7:C9),ABS(C7:C9-0)),0))

It shows the number closest to zero, out of the 3 numbers provided. How can I make this select the second closest number to zero? There will never be a tie between the numbers provided FYI.

 

[Tetra201]

Maybe

=INDEX(C7:C9,MATCH(SMALL(IF(ISNUMBER(C7:C9),ABS(C7:C9-0)),2),IF(ISNUMBER(C7:C9),ABS(C7:C9-0)),0))

 

[AlKey]

Or maybe this

=SMALL(C7:C9,IF(COUNTIF(C7:C9,0)=2,3,2))

 

[gowseph]

Thanks, this seems to work perfectly!

Maybe

=INDEX(C7:C9,MATCH(SMALL(IF(ISNUMBER(C7:C9),ABS(C7:C9-0)),2),IF(ISNUMBER(C7:C9),ABS(C7:C9-0)),0))

 

[AlKey]

Corrected:

=SMALL(C7:C9,IF(COUNTIF(C7:C9,0)=2,2,2))

 

[Tetra201]

Thanks, this seems to work perfectly!

You are most welcome.