Excel 2007 : If formula in combination with OR, AND and LOWER

Hello everybody,

I have a problem figuring out how to make a statement work.
The situation is the following:

I have several cells filled with single words. Lets say:
A1=hello
A2=wonderful
A3=world

Then I have several other fields filled with sentences e.g.:
B1=Mr. Smith says: "Hello!"
B2=The world is wonderful.
B3=Hello wonderful world.

Now I would like to have two results.

1. First of all a condition where one of the criteria is met: one of the three fields A1, A2, A3 is found in B1, B2 or B3.

I would suggest something like:
=IF(ISERROR(FIND(OR(LOWER(A1;A2;A3));OR(LOWER(B1;B2;B3));1));"No";"Yes")

The LOWER is used to be sure that we have the same letter case as we know that FIND is case sensitive. This example keeps saying that I have entered too many arguments. I am sure that there is something wrong with my syntax.

1. Second Condition would be. Detect if in one sentence all keywords A1, A2, A3 appear.

My Idea that does not work:
=IF(ISERROR(FIND(OR(LOWER(A1;A2;A3));LOWER(B1);1));"No";"Yes")

Same error.

Thanks for your help in advance!

Hello raytray,

you can use SEARCH rather than FIND if you don't want it to be case-sensitive. For the 2nd one perhaps this formula

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A$1:A$3;B1)))=3;"Yes";"No")

That'll only give you "Yes" if all three of A1, A2 and A3 are contained in B1, is that what you need?

I'm not sure I understand the first one. Are you only looking for A1 in B1, A2 in B2 and A3 in B3....and if any one of those is found return "Yes"?

I am thinking the opposite...

For question 1 you can use:

=IF(SUMPRODUCT(COUNTIF(B1:B3;"*"&A1:A3&"*"));"yes";"no")

but in your second question.. does the exact same sentence have to appear in the same order.. or just all 3 words in one cell.

Neither suggestion is order dependant so I think NBVC's should work for your first question and mine for your second...

Does the other formula work for question 1?

For question 2, are there very few words in column A to lookup...

i.e. for 3 or 4 words to find, something like:

=IF(ISNUMBER(MATCH("*"&A1&"*";B1:B3;0))+ISNUMBER(MATCH("*"&A2&"*";B1:B3;0))+ISNUMBER(MATCH("*"&A3&"*";B1:B3;0))=3;"Yes";"No")

Ok, then for formula 2 perhaps best to just tweak NBVC's version for formula 1, i.e.

=IF(SUMPRODUCT(COUNTIF(B1;"*"&A$1:A$3&"*"))=3;"yes";"no")

Yes, the * is a wildcard in COUNTIF it can stand in for any number of characters (even zero)

Yes, the * is a wildcard that can be used with functions like MATCH to look for partial strings in a larger string.

My formula assumed you wanted one formula to go through all cells in B1:B3 at once to find if all the words in A1:A3 are found. Daddylonglegs' formula checks only cell B1 for match to all 3 words in A1:A3, then you need to copy down to check B2, then B3... so you get 3 separate formulas.

I wasn't sure which you actually were looking for.