A few keystrokes shorter:

=INDEX(list,MATCH(MIN(ABS(list-target)),ABS(list-target),0))

Biff

"JMB" wrote in message
...
If it is possible to have a tie, SUMPRODUCT will sum the items in list
that
are closest to your reference number. You could also use

=INDEX(list,MATCH(TRUE,ABS(list-target)=MIN(ABS(list-target)),0))

which would return the first number in your list closest to the reference
(it must be entered with Control+Shift+Enter)

More on SUMPRODUCT and array formulas.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://www.cpearson.com/excel/array.htm


"Jordan" wrote:

I needed to know how to find the closest match to a reference number
regardless of whether its larger or smaller. I did a search and found a
post
back in March that said to use the following:

=SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list)

I applied it to my application and it works, I just have no idea why.
Can
anyone explain this formula to me or tell me where I can find a good
resource. I used the help in Excel, but didnt find it very helpful.
Thanks.