https://bugs.documentfoundation.org/show_bug.cgi?id=157778
Bug ID: 157778
Summary: Help file for MATCH function is inconsistent for
multiple occurrences of the search criterion
Product: LibreOffice
Version: 7.5.7.1 release
Hardware: All
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
The example given in the helpfile does not match the definition.
Most people see the example as the one to be followed.
In this case, the result of believing the helpfile example can be very subtle
but significant.
As I know to my cost in time and energy!
Consider the array D:D as 199,200,200,200,201 .... (no further entries)
The help file for MATCH says:-
'If Type = 1 or the third parameter is omitted, the index of the last value
that is smaller than or equal to the search criterion is returned.'
so =MATCH(200; D:D) will give the value 4
THIS IS CORRECT
BUT consider the example given
'=MATCH(200;D1:D100) searches the area D1:D100, which is sorted by column D,
for the value 200. As soon as this value is reached, the number of the row in
which it was found is returned.'
According to this example, =MATCH(3: D:D) will stop when it finds the criterion
'200' and return the row number 2.
This example is wrong!
The error becomes a problem when, for instance, looking for a particular date
when there are multiple records for each date.
FURTHER ERROR The statement says 'which is sorted by column D'
It is not necessary for the column to be sorted by Column D. In
fact, it can be in ANY order according to the definition.
Steps to Reproduce:
1.Create a sheet with D:D as stated
2.In a cell (eg A1), enter =MATCH(200;D:D)
3.Note that the answer (4) is correct according to the definition
4. Check that the example would give the number 2
5. Correct the wording.
Note that the correct wording should also cope with the situation when
there are several 200 values randomly placed
Actual Results:
as stated
Expected Results:
as stated
Reproducible: Always
User Profile Reset: No
Additional Info:
[Information automatically included from LibreOffice]
Locale: en-GB
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Windows (All)
OS is 64bit:yes
MATCH
Returns the relative position of an item in an array that matches a specified
value. The function returns the position of the value found in the lookup_array
as a number.
Syntax
MATCH(SearchCriterion; LookupArray; Type)
SearchCriterion is the value which is to be searched for in the single-row or
single-column array.
LookupArray is the reference searched. A lookup array can be a single row or
column, or part of a single row or column.
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter
is omitted, it is assumed that the first column of the search array is sorted
in ascending order. If Type = -1 it is assumed that the column is sorted in
descending order. This corresponds to the same function in Microsoft Excel.
If Type = 0, only exact matches are found. If the search criterion is found
more than once, the function returns the index of the first matching value.
Only if Type = 0 can you search for regular expressions (if enabled in
calculation options) or wildcards (if enabled in calculation options).
If Type = 1 or the third parameter is omitted, the index of the last value that
is smaller than or equal to the search criterion is returned. This applies even
when the search array is not sorted. For Type = -1, the index of the first
value that is larger than or equal is returned.
The search supports regular expressions. You can enter "all.*", for example to
find the first location of "all" followed by any characters. If you want to
search for a text string that is also a regular expression, you must precede
every character with a \ character. You can switch the automatic evaluation of
regular expression on and off in Tools - Options - LibreOffice Calc -
Calculate.
Example
=MATCH(200;D1:D100) searches the area D1:D100, which is sorted by column D, for
the value 200. As soon as this value is reached, the number of the row in which
it was found is returned. If a higher value is found during the search in the
column, the number of the previous row is returned.
--
You are receiving this mail because:
You are the assignee for the bug.
