Forum Discussion
Match on cyclic data ascending and descending back
- Non-exact matches require the entire searched column to be sorted in ascending or descending order. Your data isn't sorted and hence you cannot do a non-exact match on all of the data
Thank you for the reply. I see, that the problem lies there.
I'm just curious why it partially works...
MATCH(lookup_value, lookup_array) traverses lookup_array from its first item to its last item. It stops at the last item that is less than or equal to lookup_value.
In other words: the returned item is less than or equal to lookup_value, and the next item of lookup_array is larger than lookup_value.
This works fine if lookup_array is sorted in ascending order, or if the first part of it is in ascending order and the search ends in that part. But if the search doesn't stop when the ascending part ends, there is no item that satisfies the conditions mentioned above, so the search continues to the end of lookup_array, and MATCH returns the index number of the last item.
That's what I've thougth.
But it's not how the function is responding.
First of all, when the ascending and descending values are the same, the returned index # is of the second one. That is the one in the descending part. For example sequence 0, 1, 2, 3, 4, 3, 2, 1, 0, when searched for "2.5" will give this one 0, 1, 2, 3, 4, 3, 2, 1, 0. So it would seem, the search starts from the last item.Secondly even when the search should stop within the ascending data at some point it just breaks down and returns the last item. For the sake of argument lets assume that in our 0, 1, 2, 3, 4, 3, 2, 1, 0 sequence the "magic barrier" is at 3.1. If I search for "3" I get the index # of 3, but for 3.2 it'll return the last item.
If it is any help I can provide pictres to illustrate what I mean.
