MATCH function

[Kohei Yoshida <kyoshida@novell.com>]

Hi there,

While working on enhancing the MATCH function in OOo Calc to accept an
array as the 2nd parameter and comparing the results with Excel, I've
noticed one behavior of Excel that is not yet documented anywhere.

When the match type is 1, which assumes the source data to be in
ascending order, Excel picks the last item when a series of identical
numbers is present.  For instance, when the source data is

1
3
4
5
6
6
6
8

the formula MATCH(6, [source range], 1) returns 7.

Likewise, when the match type is -1, the source data is assumed to be in
descending order.  In this case, Excel picks the first item of the
multiple matches.  So, given the following source data,

8
6
6
6
5
4
3
1

the formula MATCH(6, [source range], -1) returns 2.

Now the real question is whether it is worthwhile to standardize this
behavior.

Kohei

-- 
Kohei Yoshida - OpenOffice.org Engineer - Novell, Inc.
<kyoshida@novell.com>