Match on cyclic data ascending and descending back

[JonasNovy]

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.

[#2 · Accepted solution]

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

[#3]

@JKPieterse 

Thank you for the reply. I see, that the problem lies there.

I'm just curious why it partially works...

[#4]

@JonasNovy 

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.

[#5]

@HansVogelaar

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.

[#6]

@JonasNovy 

Basically, the result is not reliable if the lookup column is not sorted in ascending order.

[#7]

@JonasNovy 

That could be like

If you are on Excel 265 or 2021.

[#8]

Yep, that's a good way how to put it 😄

[#9]

I'm sorry, I'm not sure if I understand correctly. Could you please explain what you mean?

[#10]

@JonasNovy 

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)
    )
)