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?

Solution 1

You could also try these two formulas:

=LOOKUP(1,0/FREQUENCY(-B1,-A1:A6),+A1:A6)
=LOOKUP(1,0/FREQUENCY(B1,A1:A6),+A1:A6)

Notes:

• the list A1:A6 does not need to be sorted
• if B1 is equal to one of the values in A1:A6 then both formulas return B1.
• if B1 lies outside the range of values in A1:A6then one of the formulas returns #N/A

Solution 2

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.