CODEWITHSUNDEEP
excelexcel-formula
NinaDev
NinaDev

Reputation: 77

Using combination of if and AND on text string in Excel

I have 3 columns: Labels/Boundary/Combo . The values for the first 2 columns are Correct, Not Correct, and Not defined. Now based on the combination of values for the first 2 columns, I want to automatically generate a value for the 3rd column, Combo. For example, if I have Correct/Correct, I will see CC in Combo column. Or if I have Not Correct/Correct, I will see NC in the Combo column. Here is the formula that I used, but it does not work. The only value that I see for column Combo is False, am I missing anything?

=IF(AND(A2="Correct",B2="Correct"),"CC", 
IF(AND(A2="Correct",B2="Not Correct"),"CN",
IF(AND(A2="Not Correct",B2="Correct"),"NC",
IF(AND(A2="Not Correct",B2="Not Correct"),"NN",
IF(AND(A2="Not Defined",B2="Not Defined"),"MM")))))

Thanks,

Upvotes: 1

Views: 48

Answers (3)

FireSarge
FireSarge

Reputation: 913

You haven't stated every possible scenario here. Since you have 2 columns with 3 values each, that gives you 3 ^ 2 = 9 different combinations, which you should nest in your "IF" statement (if you want to do it that way).

The formula you included in your question definitely doesn't cover them all, as shown in this image:

Cases covered

The "FALSE" value you see is probably the Labels/Boundary combination that is not covered by your formula. Considering all 9 cases, that formula would look like this:

=IF(AND(A2="Correct", B2="Correct"),"CC", 
IF(AND(A2="Correct", B2="Not Correct"),"CN",
IF(AND(A2="Correct", B2="Not Defined"),"CM",
IF(AND(A2="Not Correct", B2="Correct"),"NC",
IF(AND(A2="Not Correct", B2="Not Correct"),"NN",
IF(AND(A2="Not Correct", B2="Not Defined"),"NM",
IF(AND(A2="Not Defined", B2="Correct"),"MC",
IF(AND(A2="Not Defined", B2="Not Correct"),"MN",
IF(AND(A2="Not Defined", B2="Not Defined"),"MM")))))))))

I'd advise strongly against nesting 'IF' statements in this case, since this can be very difficult to maintain and prone to errors. I'd recommend you to use this formula instead, which evaluates each cell on it's own and returns the concatenation of the result of both cells.

CONCATENATE(
    IF(A2="Correct","C",IF(A2="Not Correct","N",IF(A2="Not Defined","M",""))),
    IF(B2="Correct","C",IF(B2="Not Correct","N",IF(B2="Not Defined","M","")))
)

Upvotes: 2

user4039065
user4039065

Reputation:

You should be able to simplify the formula with a couple of concatenated hard-coded lookups.

=LOOKUP(A2, {"Correct","Not Correct","Not Defined"}, {"C","N","M"})&
 LOOKUP(B2, {"Correct","Not Correct","Not Defined"}, {"C","N","M"})

Alternate,

=CHAR(CODE(A2)-ISNUMBER(SEARCH("defined", A2)))&
 CHAR(CODE(B2)-ISNUMBER(SEARCH("defined", B2)))

enter image description here

Upvotes: 1

Silver
Silver

Reputation: 3

What you have is good as far as it goes. You're seeing false when you have a set of data in the first two columns that doesn't fit any of the scenarios you've coded. You have not told it what to do when you have Not Defined in A or B coupled with either Correct or Not Correct in the other column.

=IF(AND(A2="Correct",B2="Correct"),"CC",
IF(AND(A2="Correct",B2="Not Correct"),"CN",
IF(AND(A2="Not Correct",B2="Correct"),"NC",
IF(AND(A2="Not Correct",B2="Not Correct"),"NN",
IF(AND(A2="Not Defined",B2="Not Defined"),"MM",
IF(AND(A2="Not Defined",B2="Correct"),"MC",
IF(AND(A2="Not Defined",B2="Not Correct"),"MN",
IF(AND(A2="Correct",B2="Not Defined"),"CM",
IF(AND(A2="Not Correct",B2="Not Defined"),"NM",
"not found")))))))))

The above will have enough to address the others and instead of "FALSE" will say "not found" if there is a combination of values in A and B that are not addressed.

Upvotes: 0

Related Questions