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

stribou

Copper Contributor

Feb 09, 2022

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.

SergeiBaklan

Diamond Contributor

Feb 10, 2022

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.

stribou
Copper Contributor
Feb 16, 2022
SergeiBaklan 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.