Excel nested if formula help needed!

June 1, 2009 at 07:02:53
Specs: Windows XP

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!

See More: Excel nested if formula help needed!

Report •

June 1, 2009 at 08:21:32
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)


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.

Report •

June 1, 2009 at 15:52:34
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. T

And 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!"

Report •

June 1, 2009 at 16:34:15
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.

Report •

Related Solutions

Ask Question