Forum Discussion

Ccrossed's avatar
Ccrossed
Copper Contributor
Aug 24, 2021

Help with IF and (ISNUMBER(SEARCH

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.

excel
Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 24, 2021

    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.

Resources