Question: In an excel sheet, I have from A1 to A6:

1, 2, 4, 6, 8, 9

I would like, using MATCH function, to retrieve the smallest interval that contains 5. Here, 4 and 6.

I can easily use the MATCH and INDEX function to find 4, but I can't find a way to find the 6. 

How can I reverse the order of the Array in the MATCH function?

Answer: 
You still can use the composition of `INDEX` and `MATCH` by using @ExcelHero `add one` trick but you need to make sure the matched offset doesn't overflow your index. In many use cases, you could also protect your match against an underflow. Of course, we wouldn't need all this if `MATCH` didn't request a reverse (descending) order for the -1 (Greater than) match type argument or if Excel provided a formula for reversing an array.

My suggestion is to use the following formula for the `MATCH`
 part:

```
=IF(N19 < INDEX(lookup_range, 1), 1, MIN(ROWS(lookup_range), 1 + MATCH(N19, lookup_range, 1)))

```

`N19` is the cell holding the value you look up, `lookup_range` is the name of your lookup range, the condition refers to [the first cell in the named range](https://stackoverflow.com/questions/2431381/how-do-i-reference-a-cell-within-excel-named-range).

So all in all you can just do (adapt the formulas if you don't like named ranges):

```
# For the lower limit
=INDEX(lookup_range, IF(N19 < INDEX(lookup_range, 1), 1, MATCH(N19, lookup_range, 1)))
# For the higher limit
=INDEX(lookup_range, IF(N19 < INDEX(lookup_range, 1), 1, MIN(ROWS(lookup_range), 1 + MATCH(N19, lookup_range, 1))))

```

**NOTA:** You can also change the first argument of `INDEX` in these two formulas if you're interested in any other output range.
Use XMATCH, as explained on this site:

<https://exceljet.net/formula/xmatch-reverse-search>

XMATCH allows you to set the search direction, as follows:

```
=XMATCH(B1,A1:A6,0,-1)

```

Where `B1` is the cell to match, `A1:A6` is the array you are searching through, `0` indicates "exact match", and `-1` selects searching in the reverse direction (starting with cell A6 and ending with A1).