question about match function for calc

asked 2016-01-14 18:13:02 +0100

benkmy gravatar image

I have an array, for example {3;2;1;2;3;2;1;0}, I would like to find the position with value larger than 1.5 in descending order. I use MATCH(1.5,{3;2;1;2;3;2;1;0},-1} and expecting it would return 2. However it returns value 6. It is not like what is told in help. Is it a bug?

My expectation when using MATCH() this way, MATCH() will start making the comparison to 1.5 from left (3). When it encounters the 3rd value(1) which is smaller than 1.5. it will stop and returns position of (2) which is larger than my search value (1.5).

following is copied from help:

"MATCH(SearchCriterion; LookupArray; Type)

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."

edit retag flag offensive close merge delete