Forum Discussion
Solved
Match on cyclic data ascending and descending back
Hi I'm this weird problem occured to me and I'm curious, why is this happening and if there is any way how to solve it.
I have cyclic data, i.g. it goes from 0 to 2 and back to 0 and I would like to easily select a range for example from 1 to 1.5. To do this I use two cells to input the end points and then INDEX-MATCH combination to find the closest value and finaly an exact MATCH to pin down this closest value.
It is working ok up until certain value of the higher endpoint. So when I want range 1-1.46 it is ok, but when I go for 1-1.47, the MATCH in INDEX-MATCH returns the position of the 2nd zero (e.i. the last datapoint).
I have observed that this critical value is dependent on the maximal value of the data (it's 1.46 for 0-2 and 2.9 for 0-4) and also on the step between the datapoints. Also I haven't seen it in first decending and then ascending data (going from 0 to -2 and back).
I think I can circumvent the problem by taking the ascending part of the data into an auxiliary column and searching there. Nevertheless I find this problem interesting and would like to know if anybody has experience with such a thing.
- 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
- I'm sorry, I'm not sure if I understand correctly. Could you please explain what you mean?
Misunderstood. If return both ascending and descending parts like
it could be like
=LET( nMAx, XMATCH(MAX(range), range), part, LAMBDA(list, FILTER(list, (list>=start)*(list<=end))), ascending, INDEX(range, 1):INDEX(range, nMAx), descending, INDEX(range, nMAx):INDEX(range, COUNTA(range)), VSTACK( "ascending", part(ascending), "descending", part(descending) ) )
- 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.
