|When you say So in cell1, the result would be "M," cell2's result would be "W" and cell3's result would be "", I assume you mean that the result of the formula looking at each of those would be the results you gave.|
If that's the case, then let's try this:
For an IF statement to give you the correct results, the logical_test must evaluate to TRUE or FALSE. If the logical_test evaluates to TRUE, if will return the value_if_true and if not, it will return the value_if_false.
The FIND function does not return TRUE or FALSE. It returns either the position of find_text in the find_within argument, or it returns #VALUE if find_text is not found.
Therefore, it is not a proper logical_test for an IF statement.
What you need is a function that will return either TRUE or FALSE after the FIND function is evaluated.
For that, we can use ISERROR
=ISERROR(FIND(A1,B1,1)) will return TRUE if FIND returns the #VALUE error or it will return FALSE if FIND returns the position of find_text.
Then what you need to do is put ISERROR(FIND()) inside a Nested IF to get the results you want.
A1 is the cell you want to check
B1 contains one of your check values, say M
C1 contains the other check value, say W
Put this in D1 to get the results I believe you want:
Note: You don't say what you want to happen if both of your check values are in the cell you are checking. This formula is going to look for the find_text value stored in B1 first and if it finds it, it’s going to return B1 even if the find_text value in C1 is also in A1. You'll have to incorporate an AND function in there someplace if you want to check for the presence of both values.