**Tom’s Tutorials For Excel: Finding the Number Closest to Zero**

Here are two formulas, one to tell you the number closest to zero in a list, and the other to tell you the address of the cell holding that number. When you know a list does not contain a zero (if it did, you could simply do a `VLOOKUP`

to find it), you can apply these array formulas as shown.

Recall, an array formula is applied to a cell by simultaneously pressing the `Ctrl+Shift+Enter`

keys, not just `Enter`

. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see __my video and explanation of arrays here__.

**Array formula to return the number itself:
=INDEX(C7:C20,MATCH(MIN(ABS(C7:C20)),ABS(C7:C20),0))**

**Array formula to return the cell address:
=ADDRESS(MATCH(MIN(ABS(C7:C18)),ABS(C7:C18),0)+ROW(C7)-1,3)**

The formula was very helpful. Thank you. I was trying to find the minimum number (the one closest to 0) from a group of numbers that were both + and – and I could not figure or find a formula to do it other than giving me the absolute number if it was negative. This worked. I am not sure how it works but it does. It would be great to try and understand the logic behind it.

Also I went to your link to look at your video describing an Array and the video is no longer there. I would indeed like to watch it.

Thanks again

Thanks for your comments, and for letting me know about the missing array formulas video. I’ve been doing a lot of work to expand my website and my Excel business services, and I must have messed up that link. I’ll look into it right away and send you a working link as soon as I fix it.

The video is viewable at this link:

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-videos-explaining-array-formulas/

Can you help me get the same result above but in the case where the cells are not contiguous. As in: a1,b5 and f15 contain the values -1,2 and -5. The formula should return the value -1.

I can’t figure out how to properly reference a1, b5 and f15 in place of c7:c20 in this formula:

INDEX(C7:C20,MATCH(MIN(ABS(C7:C20)),ABS(C7:C20),0))

Good question.

Do you require a native formula such as the one I posted in this example, or is a User-Defined Function (containing VBA code) acceptable.

Thanks. A native formula is needed.

Scooter,

For the specific question you asked, this would return -1

=INDEX(CHOOSE({1,2,3},A1,B5,F15),MATCH(MIN(ABS(CHOOSE({1,2,3},A1,B5,F15))),ABS(CHOOSE({1,2,3},A1,B5,F15)),0))

entered with Ctrl+Shift+Enter. You can adjust the CHOOSE({1,2,3},A1,B5,F15) (which replaces the C7:C20 in the original formula) to include more cells. For example if I wanted to add Z20 and AA4 I could use CHOOSE({1,2,3,4,5},A1,B5,F15,Z20,AA4). Each cell would need to be listed individually. so you could not have CHOOSE({1,2},A1,B5:B20)

Mighty cool. Thank you, Tom!

Thanks so much! That works perfectly, and I would never have figured that out. I didn’t even know you could use hard coded {}s in a formula. That formula is quite a mouthful … seems like Microsoft would just build a function to perform this task. Thanks again.