=IF(FIND(C9,D1,1),E1,(IF(FIND(C9,B1,1),C1,""))) So I have two big problems with my above formula. I'm trying to make a formula where if a certain cell includes what is in, for example E1 (say, the letter "T") then it should result in result one, and if it includes what is in another cell, for example C1 (say, the letter "R") then it should result in something different.

The first problem is that if the formula doesn't find either of those things, then it ends in a Value error.

The second problem is that only the first "if" in the nested "if" formula works.

Hope someone can help me figure this out!

Maybe I'm confused, but your description doesn't seem to match what the formula appears to be trying to do. Your description seems to indicate that you are checking a single cell for a value from either of 2 other cells. However, your formula has

FIND(C9,D1,1) and FIND(C9,B1,1)

FIND(find_text,within_text,start_num)

Which means it is trying to find what is in a single cell (C9) in 2 other cells (D1 and B1). Isn't that sort of opposite of what your description says?

P.S. Fixing that issue may not solve your problem, but there is no sense in suggesting a solution until we know what you are trying to do.

Hmm, okay, let me try to clarify. So I want a formula that, if the cell includes something in particular, it will come out with result1. And if it doesn't find that something in particular, it will check if the cell includes something else--if it finds that something else, it will come out with result2. And if both things aren't found in the cell, it will come out with result3 (which I am trying to make into "").

So to make an example (hopefully this will be clearer). If for example there are three cells which contain the following in order:

Cell1. M

Cell2. W

Cell3. TAnd I want to make a formula that says

1. If "M" is found in cell, then result is "M."

2.If "M" is not found in cell, then attempt to find "W."

3.If "W" is found, result is then "W." If "W" is not found, result then should be "".So in cell1, the result would be "M," cell2's result would be "W" and cell3's result would be "".

I hope that is clearer. Again, the problem with my formula is that the first "if" function, which does what would be the first part of the formula succeeds, but not the rest of it. Instead, it comes up with "#VALUE!"

When you say So, I assume you mean that the result of the formulaincell1, the result would be "M," cell2's result would be "W" and cell3's result would be ""looking ateach 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_testmust evaluate to TRUE or FALSE. If thelogical_testevaluates to TRUE, if will return thevalue_if_trueand if not, it will return thevalue_if_false.The FIND function does not return TRUE or FALSE. It returns either the position of

find_textin thefind_withinargument, or it returns #VALUE iffind_textis not found.Therefore, it is not a proper

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

Assume:

A1 is the cell you want to check

B1 contains one of your check values, say M

C1 contains the other check value, say WPut this in D1 to get the results I believe you want:

=IF(ISERROR(FIND(B1,A1,1)),IF(ISERROR(FIND(C1,A1,1)),"",C1),B1)

Note: You don't say what you want to happen if

bothof your check values are in the cell you are checking. This formula is going to look for thefind_textvalue stored in B1 first and if it finds it, itâ€™s going to return B1 even if thefind_textvalue 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 ofbothvalues.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History