Forum Discussion
Issues with Reverse Search in XMATCH
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 ent...
HansVogelaar 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
https://diamondoffshore-my.sharepoint.com/:x:/g/personal/stribou_dodi_com/EYcNF47kxRJFvQjzoEzeGjkBUnbTzUj68n7Q6kGE0Pvgsg?e=o8yjaG
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.