mercredi 20 mai 2020

VLookup with Multiple Ranges

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,

Aucun commentaire:

Enregistrer un commentaire