# Thread: Nested IF/AND with lots of ORs (2003)

1. ## Nested IF/AND with lots of ORs (2003)

I'm trying to create a formula for the following:

IF K15 = "Spontaneous" and 'New Case'!K5="SP" OR "L" OR "HA" OR "SR" OR "SH","OK","Error"
OR
IF K15 = "Literature (Spon)" and 'New Case'!K5="L","OK","Error"
OR
IF K15 = "Spontaneous from HA" and 'New Case'!K5="HA","OK","Error"
OR
IF K15 = "Stimulated (retro/Disease)" and 'New Case'!K5="SR","OK","Error"
OR
IF K15 = "Not Available" and 'New Case'!K5="NA","OK","Error"
OR
IF K15 = "Clinical Trial" and 'New Case'!K5="CS" OR "CU","OK","Error"

2. ## Re: Nested IF/AND with lots of ORs (2003)

Try

=IF(OR(AND(K15="Spontaneous",OR('New Case'!K5="SP",'New Case'!K5="L",'New Case'!K5="HA",'New Case'!K5="SR",'New Case'!K5="SH")),AND(K15="Literature (Spon)",'New Case'!K5="L"),AND(K15="Spontaneous from HA",'New Case'!K5="HA"),AND(K15="Stimulated (retro/Disease)",'New Case'!K5="SR"),AND(K15="Not Available",'New Case'!K5="NA"),AND(K15="Clinical Trial",OR('New Case'!K5="CS",'New Case'!K5="CU"))),"OK","Error")

(This will be displayed on several lines, but it is one formula)

<img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

3. ## Re: Nested IF/AND with lots of ORs (2003)

Perfect.

Now to disect/analyse so that I can apply it to others...

<img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

4. ## Re: Nested IF/AND with lots of ORs (2003)

Hi musical,

Here's another two renditions, both slightly shorter:
=IF((K15="Spontaneous")*NOT(ISERROR(MATCH('New Case'!K5,{"SP","L","HA","SR","SH"},0)))+(K15="Lite rature (Spon)")*('New Case'!K5="L")+(K15="Spontaneous from HA")*('New Case'!K5="HA")+(K15="Stimulated (retro/Disease)")*('New Case'!K5="SR")+(K15="Not Available")*('New Case'!K5="NA")+(K15="Clinical Trial")*NOT(ISERROR(MATCH('New Case'!K5,{"CS","CU"},0)))>0,"OK","Error")

=IF(OR(AND(K15="Spontaneous",NOT(ISERROR(MATCH('Ne w Case'!K5,{"SP","L","HA","SR","SH"},0)))),AND(K15=" Literature (Spon)",'New Case'!K5="L"),AND(K15="Spontaneous from HA",'New Case'!K5="HA"),AND(K15="Stimulated (retro/Disease)",'New Case'!K5="SR"),AND(K15="Not Available",'New Case'!K5="NA"),AND(K15="Clinical Trial",NOT(ISERROR(MATCH('New Case'!K5,{"CS","CU"},0))))),"OK","Error")

Both renditions have array matches (eg MATCH('New Case'!K5,{"SP","L","HA","SR","SH"},0)) where you have multiple criteria, which make it easier to add/delete criteria as the need arises. You'll probably find the second version slightly easier to digest - the first one replaces the AND & OR expressions with * and +, respectively.

5. ## Re: Nested IF/AND with lots of ORs (2003)

When I first saw your post, I thought to myself... "beginner"... stick to the basics and what you almost know.

Then, wouldn't you know, I maxed out the space avaliable in the cell (formula too long).

In parts of the formula, I have NO clue what it was "doing" (example, the ',0' at the end of each phrase), but plagerizim at it's best, I created the following (and got it correct on the first try):

=IF(OR(AND(E15="Clinical Trial",E18="Associated",Events!E20="Y",Events!E22= "Y"),
AND(E15="Clinical Literature",E18="Associated",Events!E20="Y",Events !E22="Y"),
AND(E15="Clinical Trial",E18="Not Associated",Events!E20="N",OR(Events!E22="N",Event s!E22="NRP")),
AND(E15="Clinical Literature",E18="Not Associated",Events!E20="N",Events!E22="N"),
AND(E15="Clinical Literature",E18="----",NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK "},0)))),
AND(E15="Spontaneous",NOT(ISERROR(MATCH(Events!E20 ,{"PRO","POS","UNL","UNC"},0)))),
AND(E15="Literature (Spon)", NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK"} ,0)))),
AND(E15="Spontaneous from HA", NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK"} ,0)))),
AND(E15="Stimulated (retro/Disease)", NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK"} ,0)))),
AND(E15="Not Available", NOT(ISERROR(MATCH(Events!E22,{"Y","N","NRP","UNK"} ,0))))),"OK","Error")

Many, many thanks,
Jody

6. ## Re: Nested IF/AND with lots of ORs (2003)

Hi Jody,

Congratulations!

The ',0' at the end of each MATCH function tells that function to use 'exact' matching; otherwise, false matches might be returned. The MATCH function supports 'exact', 'nearest equal to or above' and 'nearest equal to or less' matching. See Excel's help file for more details. With 'exact' matching, the MATCH function will return an error value (#N/A!) if no match is found. Using the ISERROR function tests whether the MATCH function returned an error (1=error, 0 = no error) and the NOT function then reverses the ISERROR results (because we want to use the 'true' matches).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•