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 entered as "1" or "-1" - any ideas as to what might be occurring here?
Any help will be greatly appreciated!
7 Replies
- 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.
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.
Could you attach a sample workbook demonstrating the problem, or make it available through OneDrive, Google Drive, Dropbox or similar?
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.
