VLookup with Multiple Ranges

Question

I'm trying to make a formula that would do the following: There are say 10 categories 1-10, given a number x and y, the line is in category 3 if and only if x is between 1 and 2 and y is between 5-7 for example. I don't know how to use VLookup given the multiple conditions and the two ranges that are completely different and not in a sequential order.

I tried using index match:

=INDEX(B5:B15,MATCH(1,IF(AND(K5>=C5:C15,K5<=D5:D15),1,0)*IF(AND(L5>=E5:E15,L5<=F5:F15),1,0),0))

but this returns an error where column B are the categories, K5 and L5 are x and y respectively and column C is the lower bounds for x per category with D as upper bounds and same for E and F for y.

Here's a mock representation of the data and rules:

Data
x     y   category
1.2   12     1
1.5   5      2
0.98  23     3
.
.
.

Rules
Category X-LB X-UB Y-LB Y-UB 
1         1    2    9    15
2         1.5  1.7  1     9
3         0.8  1    20    23
.
.
.

LB is lower bound and UB is upper bound. For example given x and y above using the rules table we find the expected return column.

Thank you,

Answer 1

In M5, copied down :

=INDEX($B$5:$B$15,MATCH(1,INDEX(($K5>=$C$5:$C$15)*($K5<=$D$5:$D$15)*($L5>=$E$5:$E$15)*($L5<=$F$5:$F$15),0),0))

Or,

Another shorter option.

Using SUMIFS function, formula in M5 copied down :

=SUMIFS($B:$B,$C:$C,"<="&$K5,$D:$D,">="&$K5,$E:$E,"<="&$L5,$F:$F,">="&$L5)

Answer 2

If you have only category which will fit the bill in each case, one way is to use SUMPRODUCT.

Formula in C2 and down is

=SUMPRODUCT(($B$10:$B$12<=A2)*($C$10:$C$12>=A2)*($D$10:$D$12<=B2)*($E$10:$E$12>=B2)*($A$10:$A$12))