Help with IF and (ISNUMBER(SEARCH

%3CLINGO-SUB%20id%3D%22lingo-sub-2680687%22%20slang%3D%22en-US%22%3EHelp%20with%20IF%20and%20(ISNUMBER(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2680687%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20you%20guys%20could%20help%20me%20with%20a%20complex%20problem%20using%20IF%20and%20pieces%20of%20text.%3C%2FP%3E%3CP%3EBelow%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(J21519%3DISNUMBER(SEARCH(%22PILOT%22%2CJ21519))%2COR(Q21519%3DISNUMBER(SEARCH(%22Matam%22%2CQ21519))%2CX21519%3DISNUMBER(SEARCH(%22Matam%22%2CX21519))%2CS21519%3DISNUMBER(SEARCH(%22MAM%22%2CS21519))%2CZ21519%3DISNUMBER(SEARCH(%22MAM%22%2CZ21519))))%2C%22Verify%22%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20attempting%20to%20show%20%22verify%22%20if%3A%3C%2FP%3E%3CP%3Ecell%26nbsp%3B%3CSTRONG%3EJ21519%3C%2FSTRONG%3E%20has%20%22pilot%22%20in%20it%20AND%20any%20one%20of%20the%20following%20is%20true%3A%3C%2FP%3E%3CP%3Ecell%26nbsp%3BQ21519%20has%20%22Matam%22%3C%2FP%3E%3CP%3Ecell%26nbsp%3BX21519%20has%20%22Matam%22%3C%2FP%3E%3CP%3Ecell%26nbsp%3BS21519%20has%20%22mam%22%3C%2FP%3E%3CP%3Ecell%26nbsp%3BZ21519%20has%20%22mam%22.%3C%2FP%3E%3CP%3EOtherwise%2C%20show%20a%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20it%20only%20shows%200%20even%20after%20adding%20one%20of%20the%20conditions%20above%20that%20should%20make%20it%20print%20a%20%22verify%22.%20Also%2C%20I'm%20planning%20on%20making%20it%20an%20IFS%20function%20as%20many%20more%20of%20these%20(messy)%20formulas%20will%20be%20added%20for%20every%20variable%20on%20cell%26nbsp%3B%3CSTRONG%3EJ21519%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2680687%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2681160%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20IF%20and%20(ISNUMBER(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2681160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128056%22%20target%3D%22_blank%22%3E%40Ccrossed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESEARCH(%22PILOT%22%2CJ21519)%20returns%20position%20of%20%22PILOT%22%20in%20text%20or%20error%20if%20nothing%20is%20found%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EISNUMBER(SEARCH(%22PILOT%22%2CJ21519))%20returns%20TRUE%20or%20FALSE%20depends%20on%20was%20%22PILOT%22%20found%20or%20not%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EJ21519%3DISNUMBER(SEARCH(%22PILOT%22%2CJ21519)%20means%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3D(J21519%3DTRUE)%20or%26nbsp%3B%3D(J21519%3DFALSE)%20which%20always%20returns%20FALSE%20since%20in%26nbsp%3BJ21519%20you%20have%20some%20text%2C%20not%20Boolean%26nbsp%3Bvalue.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThus%20simply%20use%26nbsp%3BISNUMBER(SEARCH(%22PILOT%22%2CJ21519)%20as%20condition.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello!

 

I was wondering if you guys could help me with a complex problem using IF and pieces of text.

Below formula: 

=IF(AND(J21519=ISNUMBER(SEARCH("PILOT",J21519)),OR(Q21519=ISNUMBER(SEARCH("Matam",Q21519)),X21519=ISNUMBER(SEARCH("Matam",X21519)),S21519=ISNUMBER(SEARCH("MAM",S21519)),Z21519=ISNUMBER(SEARCH("MAM",Z21519)))),"Verify",0)

 

Is attempting to show "verify" if:

cell J21519 has "pilot" in it AND any one of the following is true:

cell Q21519 has "Matam"

cell X21519 has "Matam"

cell S21519 has "mam"

cell Z21519 has "mam".

Otherwise, show a 0.

 

So far it only shows 0 even after adding one of the conditions above that should make it print a "verify". Also, I'm planning on making it an IFS function as many more of these (messy) formulas will be added for every variable on cell J21519.

1 Reply

@Ccrossed 

SEARCH("PILOT",J21519) returns position of "PILOT" in text or error if nothing is found

ISNUMBER(SEARCH("PILOT",J21519)) returns TRUE or FALSE depends on was "PILOT" found or not

 

J21519=ISNUMBER(SEARCH("PILOT",J21519) means

=(J21519=TRUE) or =(J21519=FALSE) which always returns FALSE since in J21519 you have some text, not Boolean value.

 

Thus simply use ISNUMBER(SEARCH("PILOT",J21519) as condition.