MATCH reverse order

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 1

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.

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.