Help with IF and (ISNUMBER(SEARCH

Copper 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.