Need Help, I need to create formula to categorize data containing - Microsoft Community

hello all, 

i have created formula categorize below table, not accurate, need fix up.

data , scenario follows:

col1	col2
5	this automated message
4	this automated message
2	this automated message
1	this automated message
2	this automated message
0	this automated message
1	this automated message
2	this automated message

here scenario:

1. col1 has integers, random integers (note: data type of col1 not changed numeric or anything, has default data type)

2. col2. has various messages, here lets take 1 message, "this automated message".

i want create alogrithm, categorize data where, if data in col2 has value "this automated message" , value of col1 <=3, should categorize data "check" or else "ok"

here formula have created, want enhance correct/better one.

=if(and(isnumber(search("*this automated message*",col2)+(search("0",col1)))),"check",if(and(isnumber(search("*this automated message*",t2)+(search("1",r2)))),"check","ok"))

kindly out.

thank you!

hi,

maybe this: =if(and(isnumber(search("this automated message",b2)),a2<=3),"check","ok")

Office / Excel / Windows 8 / Office 2010