Finding the closest number on a column

I have not been able to find the correct formula to find the closest number(higher or lower) from a column with diferent values. I have only found formulas for finding a value Higher or formulas for finding a value lower, but i need excel to find the closest (higher or lower). Thanks for the help in advance.

If you have a number in B1 and you want to find the nearest number to that in A1:A10 (higher or lower) then try this formula

=INDEX(A1:A10,MATCH(MIN(ABS(B1-A1:A10)),ABS(B1-A1:A10),0))

confirmed with CTRL+SHIFT+ENTER

Originally Posted by daddylonglegs

If you have a number in B1 and you want to find the nearest number to that in A1:A10 (higher or lower) then try this formula

=INDEX(A1:A10,MATCH(MIN(ABS(B1-A1:A10)),ABS(B1-A1:A10),0))

confirmed with CTRL+SHIFT+ENTER

This worked great, thanks daddylonglegs. I wanted to work with this on my Pocketpc that has windows mobile 5 with excel but when I transfer the file, the array disapears, when I try to redo it on the pocketpc it does not work. Is there a work around? Anyway Thanks

Originally Posted by daddylonglegs

If you have a number in B1 and you want to find the nearest number to that in A1:A10 (higher or lower) then try this formula

=INDEX(A1:A10,MATCH(MIN(ABS(B1-A1:A10)),ABS(B1-A1:A10),0))

confirmed with CTRL+SHIFT+ENTER

Is there any way of doing this without it being an array?

Anyone????

I'm trying to find the number closest to 50% in the same column then conditionally format that cell...any help?