Aug 24 2021 12:41 PM
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.
Aug 24 2021 03:01 PM
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.