I don't mean to be a pita, but.........

SUMPRODUCT(((ABS(list-target))

evaluates as an array, but

MIN(ABS(list-target))

doesn't.

In my "brain seizure" post you'll see where if you swap the position of ABS
and MIN:

ABS(MIN(list-target))

then it is evaluated as array and can be normally entered although the logic
is incorrect and can lead to incorrect results.

Just trying to figure this out.

Biff

"Bob Phillips" wrote in message
...
Because of the ABS

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Biff" wrote in message
...
=SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list)
That formula only works if it's entered as an array.

The superfluous ( ) aside, why doesn't that formula work if normally
entered?

=MIN(ABS(list-target))

Doesn't evaluate as an array unless array entered.

Biff

"Bob Phillips" wrote in message
...
Sorry, forgot this bit.

Taking BIff's formula

ABS(list-target) creates an array of values which forces the closest
matching n umber to be the smallest in the array (as it makes smaller
numbers absolute, for instance if the list has 1,2,3,4,5 and target is
3.3,
this creates an array of {2.3;1.3;0.3;0.7;1.7;3.3;3.3;3.3;3.3;3.3}- you
can
see the third item is 0.3.

MIN(ABS(list-target) gets the smallest value there - 0.3 in the
example

This minimum value is matched against the same list
MATCH(MIN(ABS(list-target)),ABS(list-target),0), which returns the
index
of
that smallest number in the list

This index is then used to get the value from the list INDEX(list,
index_of_smallest,0)

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"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.