Can't detect keyword in cell to display NA

[kwangyeow]

Hi All,

I think my formula is wrong, but not sure where or how to do it to make it show NA at K12.
It suppose to show NA when J12 is empty and N12 has the keyword exchange.

=IF(C12="LR","LR",IF(C12="TOP","TOP",IF(A12<>"",IF(AND(J12=""&IF(ISNUMBER(SEARCH("*exchange*",N12)),"NA",IF(J12="","Not Yet",IF(J12>=A12,"Return")))),""))))


Can someone enlighten what went wrong ?

Google link: https://drive.google.com/open?id=1X1ey_WKMA0c_WYALVrF1rYjVIU6iswzE

Thanks !!

 

[thisoldman]

You have misplaced parentheses.

=IF(C12="LR",
  "LR",
  IF(C12="TOP",
    "TOP",
    IF(A12<>"",
      IF(AND(J12="" & IF(ISNUMBER(SEARCH("*exchange*",N12)),
                        "NA",
                        IF(J12="",
                          "Not Yet",
                          IF(J12>=A12,
                            "Return")))),
        ""))))

I do not understand what you are attempting to do with the AND() function. I have highlighted the AND's opening and closing parentheses.

 

[Marcílio_Lobão]

kwangyeow, Good morning.

If is not a misplaced parentheses maybe you need to complete a clause FALSE of 2 IFs where I completed with "missing part 1" and "missing part 2".

I shortened your formula too.

Try to use:

=IF(OR(C12="LR",C12="TOP"),C12,IF(A12<>"",IF(J12="",IF(ISNUMBER(SEARCH("*exchange*",N12)),"NA","Not Yet"),IF(J12>=A12,"Return","missing part 1")),"MISSING PART 2"))

Is that what you want?

I hope it helps.