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)

Not a bug. From XL Help ("MATCH"):

If match_type is 1, MATCH finds the largest value
that is less than or equal to lookup_value.

*****Lookup_array must be placed in ascending
order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.*****





In article ,
jhgravelle wrote:

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)

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)

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)

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)

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)

From that article:

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.

Well, that's poorly worded. Those functions do default to that type of match
*IF* the range_lookup/match_type is not defined (or omitted). If you notice,
all the formula examples used in that article have *omitted* the
range_lookup/match_type and the sample data is *sorted in ascending order*.

If you want the largest value that is less than or equal to the lookup_value
and the data is not sorted you need to use a different formula but you need
to define what should happen on the lower end of the scale. For example,
what should happen if there is no value less than or equal to the
lookup_value? By default, this formula would return 0:

Array entered** :

Returns the value:

=MAX(IF(A1:A10<=B1,A1:A10))

Returns the relative position but returns an error if there is no value less
than or equal to the lookup_value:

=MATCH(MAX(IF(A1:A10<=B1,A1:A10)),A1:A10,0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


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