# In which direction is the "MATCH function (Type -1)" searching?

Is the “MATCH function (Type -1)” searching

• In rows: from right to left (or the other way around)?
• in Column: From top to bottom (or the other way around)?

The function call could be “`=MATCH(4; C1:F1; -1)” (The return is “3” for an array: 40, 16, 5, 3)

(it looks like it is searching from left to right)

-1 requires that the search range is sorted in descending order…thats all.

the returned Value is always the position count from left|top

But if You read the Calc-Manual for the MATCH function:

It says For “Type = -1” the function returns the index of the first value that is larger [,] the search criterion

You say it count/search from Left - so the first value that is larger […]to the search criterion “4" is “40” in the descending array 40, 16, 5, 3

The help-manual is ambigous,
…with -1 it returns the position of the Value equal to criterion or the position of the closest match greater than criterion.

Are we 100% sure that the “help-manual” here is ambigous - I would say misleading

If Type = 1 or the third parameter is missing, the index of the last value that is smaller or equal to the search criterion is returned. This applies even when the search array is not sorted. For Type = -1, the first value that is larger or equal is returned.

This part of the MATCH function help page is incorrect. MATCH(4,{2,11,4,5},1) will most certainly not return 3, it will return 1. I think the sentence “This applies even when…” was meant to indicate that you aren’t going to get back an #N/A just because the array is not sorted??

The LO MATCH seems in every way to replicate the MS Excel function, as promised earlier on the help page. So:

Type 1: go LtoR/TtoB until you hit the search criterion and give me that position or hit something bigger than the search criterion and give me the position right before that, or #N/A if there is no position before that because the first entry is already bigger. If nothing is equal to or bigger than the criterion, then give me the last position.

Type 0: go LtoR/TtoB until you match the search criterion exactly and give me that position, or else give me #N/A if there is no exact match.

Type -1: go LtoR/TtoB until you hit the search criterion and give me that position or hit something smaller than the search criterion and give me the position right before that, or #N/A if there is no position before that because the first entry is already smaller. If nothing is equal to or smaller than the criterion, then give me the last position.

Also, the help page states:

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 missing, 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 in sorted in descending order. This corresponds to the same function in Microsoft Excel.

Here, the words “first column” are confusing. First, the paragraph before it allows for a row (and obviously MATCH works for a row) and, second, why say “first” if only one column is allowed? More than one column will result in a Err:504, in fact.

there is no need to post false use of MATCH, if you use 1 as last criteria you MUST have a monotonic ascending search array

The irregular MATCH was part of a comment, not a solution. The comment was expanding directly on the question in the previous comments, namely, the correctness of the man page on MATCH. It showed that the man page, which itself mentions unsorted data for type=1, was not correct, and so there was a purpose for posting it.

Also, knowing the behavior under irregular circumstances can definitely help lead a person to what is causing that behavior if it ever turns up in troubleshooting. For example, if you are getting an #N/A for a use of MATCH and don’t know why in some complex scenario, knowing that it likely comes from your first data point could be handy. I do agree that there is never a reason for a ‘constant array’ that is not in order, but that was just a shorthand for demonstration purposes.

Neither. It performs a binary search (which is the reason it gives arbitrary results if the range is not sorted according to the third argument) in a vector. The position/index returned is counted from top in a column vector or left in a row vector.

As others noted, the help is wrong. It should read

If Type = 1 or the third parameter is missing, the index of the last value that is smaller or equal to the search criterion is returned. For Type = -1, the index of the last value that is larger or equal is returned.

Fixed in now current source/text/scalc/01/04060109.xhp - help - Gitiles

Of course - That was why I was posting it - I thought the help text was wrong - But I am new to the MATCH-function - so I had to be absolute sure

For 6 hour ago I sent a mail to them with the following suggest to a correction:

Original: For Type = -1, the first* value that is larger or equal is returned

Suggestion: For Type = -1, the function returns the array number, whose last value is larger or equal to the search criterion.

Ooh I can see you are “LibreOffice Calc developer” - So I might have sent you a mail

I don’t know where you sent the mail to, I just fixed that on the fly when I read this thread.

Good, something good come out of my efforts and wondering