IF AND THEN Formula

[tomvh444]

I am trying to get the correct formula for the following:

IF A7 is "no" AND B7 is between 100 and 150, AND C7 is "xx", THEN IF A8 is "yes" AND B8 is between 100 and 150, AND D8 is "xx", THEN E8 ="xx", AND IF A8 is "yes" AND B8 is between 100 and 150, AND D8 is "yy", THEN E8 = "yy"

Column A will always be either "no" or "yes" Column B will only have a number in it but that number may be lower than 100 and can even be a negative number or higher than 150. Column C will always be either "xx" or "yy" Column D will always be either "xx" or "yy" Finally if Column E does not = either "xx" or "yy" then it remains blank.

[ThirdFret]

You're missing a few cases in your example. You haven't described what you'd like the output to be if your first set of AND statements is false, or if your first set is true and your second set is false.

In my formula below, I've added text for those two cases so you can replace that text with whatever you think should be the output in those scenarios.

Please Login or Register  to view this content.

Hope that helps!

[tomvh444]

I have not tested your solution yet. I wanted to first see I can clarify your request regarding the missing cases. If the first set of AND statements is false then it does not even look at Row 8. IF the first set of AND statements is TRUE but any one of A8, B8, or D8 is FALSE then it looks no further, and E8 remains blank. Sorry for not clarifying this part.

Base on this further clarification, would you still suggest the same formula?

[ThirdFret]

I suggest trying the provided formula with your data.

The part that confuses me is that this formula will never return anything except "yy" or blank. Based on your explanation I would expect a desired result of "xx" in some cases.

Please Login or Register  to view this content.

[tomvh444]

Does not this part of my original request make for E8 to possibly be "xx"

THEN IF A8 is "yes" AND B8 is between 100 and 150, AND D8 is "xx", THEN E8 ="xx"

[ThirdFret]

There we go! I knew I was missing some information. Try the following formula.

Please Login or Register  to view this content.

[tomvh444]

Thanks, Definitely appears to be working. This will be a nice time saver. Will mark as solved and add to rep.

[ThirdFret]

Thanks!

Glad to help.