Issues with Reverse Search in XMATCH

[stribou]

Hello - I have an application for which I am trying to utilize XMATCH's reverse function.  Unfortunately I get the formula returns the same value regardless of whether the search mode argument is entered as "1" or "-1" - any ideas as to what might be occurring here?

Any help will be greatly appreciated!

[#2]

@stribou 

Could you attach a sample workbook demonstrating the problem, or make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

May be it is because the value you are searching is in the reference list As per syntax Exact match or next smaller item and Exact match or next larger item. "Exact Match" gives same.

[#4]

@Hans Vogelaar   Thank you for the response.  Please see below link.  I've added a simple table showing the formula result as compared to the expected result

XMATCH Reverse Example.xlsx

[#5]

@jitinm thank you very much for the response. Unfortunately I have not been using Exact Match so I do not believe this is the issue.

[#6]

@stribou 

Thanks! As far as I can tell, the reason is that non-exact matches only work as expected if the lookup range is sorted (this is not mentioned in the documentation!)

Let's take =XMATCH($D$2,F$15:F$137,-1,-1)

The search starts from the bottom up, looking for an exact match first. This is not found, so we end at the top of the range, in F15. Since an exact match was not found, Excel now moves down again and stops at F89, the last value <= D2.

[#7]

@stribou 

I'm missed, formula correctly returns positions ## 75 and 76 within the range, why 86 and 123 are expected?

We may sort the range and check what is before and after the 25.04177

IMHO, XMATCH doesn't require sorting.

[#8]

@Sergei Baklan  Thanks for your contribution.  We expect values of 86 and 123 because we have designated the search mode as reverse / -1.  My understanding is that the function should begin with the last value in the array and work towards the first.  When the match mode is 1 (greater than or equal to the match value), the function should return the last value in the array (#123) as it is greater than the match value of 25.

When the match mode is -1 (less than or equal to match value), it should return #86 as this is the first value when search in reverse that is less than 25.

At least this is how I expected the function to work but perhaps I am mistaken.