Need a multi-conditional index match statement with > and < criteria

MATCH can't use relational operators but it does have three options for how it matches. There is a good description of it in Excel Help.

match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

The following table describes how the function finds values based on the setting of the match_type argument.

Match_type: Behavior

1 or omitted: MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

0: MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.

-1: MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

Can you give more detail about how your data is organized and what you are trying to do?