Thread: How to satisfy multiple conditions using multiple criteria

Hi everyone

I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.

So, overall here is what I want:

If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6

These 6 conditions cover all possible combinations of numbers in the two columns.

Any help in mashing all this into one beautiful formula would be greatly appreciated!

Perhaps

=MATCH(DL2+(CK2/10),{3.1,3.2,1.2,2.2,1.1,2.1},0)

The match method makes use of the fact the source values are numbers, range is known and each combination can be made to be unique

DL2=3 and CK2=1 --> 3 + 1/10 -> 3.1
DL2=3 and CK2=2 --> 3 + 2/10 -> 3.2
DL2=1 and CK2=2 --> 1 + 2/10 -> 1.2
DL2=2 and CK2=2 --> 2 + 2/10 -> 2.2
DL2=1 and CK2=1 --> 1 + 1/10 -> 1.1
DL2=2 and CK2=1 --> 2 + 1/10 -> 2.1

You can then match the current value against these 6, the MATCH will return the position of the MATCH within the array of values, so

=MATCH(DL2+(CK2/10),{3.1,3.2,1.2,2.2,1.1,2.1},0)

will return the position that DL2+(CK2/10) is found... by ordering the values in the inline array correctly you will get the appropriate 1-6 return.

Hi again,

Just to clarify: from what I understand, dividing the second number by 10 is used to make each combination total a unique number? So, theoretically you could use any number besides 10?

Edit: Hmm looking at it again maybe I am way off. It's just the /10 that I am not clear on its purpose

The /10 is used to ensure each combination of DL/CK is unique yes, for ex. assume

DL2 & CK2 are 1 & 2
DL3 & CK3 are 2 & 1

A simple addition of the two would generate 3 in both cases - not trying to be condescending but purely for sake of demo of logic:

1 + 2 -> 3
2 + 1 -> 3

which would cause obvious issues.

We use 10 because simply because it ensures CK when divided can never exceed 1 (given possible values of CK), eg

1 + 2/10 -> 1.2
2 + 1/10 -> 2.1

The key is to ensure the 2nd value never > 1 else you impact the first value making results void, for ex. if we used divisor of 2 rather than 10

1 + 2/2 -> 2
2 + 1/2 -> 2.5

This could lead to issues going forward ... in essence we want to be sure that neither DL & CK can be affected by one another.

Does that make sense ?

One more way to go:

=LOOKUP(1*(D1&E1);{11;12;21;22;31;32};{5;3;6;4;1;2})

Also, please make thread [solved] as DonkeyOte provide you solution...

Cool, thank you zbor, another good idea.

I'm sorry, how do I change the post to say resolved?

Edit: Nevermind, figured it out finally