Forum Discussion

Copper Contributor

Feb 09, 2022

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

excel

Formulas and Functions

HansVogelaar

MVP

Feb 09, 2022

stribou

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

stribou
Copper Contributor
Feb 09, 2022
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
- HansVogelaar
  MVP
  Feb 09, 2022
  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.