MATCH-Function should return a "index" but returns "#N/A

English
,
#1

I try to fin out what is up and down with the MATCH-Function
I can read at LibreOffice 7.2 Help the following: " 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"

Now I got this simple column (please see the attached
A1 = 12
A2 = 10
A3 = 5
A4 = 9
A5 = 40

This is the MATCH-Function: =MATCH(9; A2:A5; 1)

Should it not return “2” which is “the last value that is smaller or equal to the search criterion”
¤PC1905-

#3

is for first column array ascending. Click in A7 and press Ctrl+F2 to see the Function Wizard. Why not 0, instead of 1.
Cell A1 don’t participate in this match?

#4

Right. Therefore the first value 10 in A2 is bigger than the search value and the search stops without any match. Same with A1:A5. In spreadsheet mode we always get some match unless the search vector’s first value is smaller than the search value.

1 Like
#5

Just try to under stand the help-file by doing
Yes it is ascending and therefore (as already wroted) it shoud return the index of the last value that is smaller or equal to the search criterion is returned

Let me ask in another way
¤PC1905-
If i try to do “=MATCH(11 A2:A5; 1)`” then the function returns “3” This is in accordance with the help text - It searches the column array ascending and stops at the value 9 (in A4) because A5 = 40 is higher than the search criterion and returns (the relative) row number: 3

But the =MATCH(8; A2:A5; 1)is not behaving like that I Don’t understand why it not returns “2” cause that would also be in accordance with the help text - It searches the column array ascending and stops at the value 5 (in A3) because A4 = 9 is higher than the search criterion (8) and returns (the relative) row number: 2
But NO it returns “#N/A”

#6

10 (A2) is greater than 8, so the search stops before it began.

1 Like
#7

The sequence you try to find a match in is 10; 5; 9; 40

. Thisd is not ascending. At least i6t isn’t in the sense assumed here. You may convert the term to the more precise “Monotonic Ascending Step by Step From the First to the Last Element” (This does not include a “strictly”. A step is allowed to be 0. In this case the higher index is returned.) .

#8

Of cause - silly me - forgot to skip A1 (12) - sorry to disturb

#9

A2 was the culprit in the given example.

1 Like
#2

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.