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