Well to save some face. I'm not totally nuts. It appears that excel 2005
worked "By default, the VLOOKUP(), HLOOKUP(), and MATCH() functions work on
an "entry before the first entry that exceeds the key entry" basis€”not by
exact match."
http://www.microsoft.com/technet/arc....mspx?mfr=true
I think excel 2000 and even excel 2003 still allows the same "entry before
the first entry that exceeds the key entry" method when using 1 or -1. But I
can't confirm that until i can try that in 2000 or 2003.
"Ragdyer" wrote:
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)