With a match type of " -1", you'll get a value larger then or equal to the
lookup value,
BUT ... even in that case, the lookup array must be sorted, though that sort
must be *descending*.

The function has not changed with the differing versions.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jhgravelle" wrote in message
...
Thanks guys,

didn't prior versions allow the array to be unsorted, and to find the
value
before the first value that was greater than the lookup value?

"T. Valko" wrote:

Not a bug.

Since you're using a match_type argument of 1 this *requires* the
lookup_array to be sorted in ascending order for the function to work
properly. The fact that it returned the correct result in your second
example is just "dumb luck".

--
Biff
Microsoft Excel MVP


"jhgravelle" wrote in message
...
Can anyone else recreate a similar error in the result of the match
formula,
or am I doing something wrong?

the following formula is producing a result of 5, I would have
expected a
value of 1.

=MATCH(B$35,$B32:$F32,1)

=MATCH(0.4,{0.366666666666667,0.71,0,0,0},1)


now if i edit the values in $B32:$F32 i get the right answer answer 1

=MATCH(0.4,{0.366666666666667,0.71,0.71,0,0},1)


but now another formula isn't working, I get an answer of 3, and would
expect the answer 2

=MATCH(C$35,$B32:$F32,1)

=MATCH(0.71,{0.366666666666667,0.71,0.71,0,0},1)