Hi!

Hmmm...

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

That formula only works if it's entered as an array. But, if you change it
around to:

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

Or, how I would write it:

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

And enter it normally, it works

Can anyone explain that?

Biff

"Jordan" wrote in message
...
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.