Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

how to simplify this IF OR AND functions

Posted on 2016-10-06
49 Views
i have this long formula.

=IF(OR(AND(A2=1,B2=9),AND(A2=2,B2=12),AND(A2=3,B2=18),AND(A2=4,B2=15),AND(A2=5,B2=22),AND(A2=6,B2=95),AND(A2=12,B2=19),AND(A2=42,B2=52)),"","Ok")

is there any easy way or more simplistic way to achieve the same without these too many ORs and ANDs ? or make it work in a better way.
0
Question by:excelismagic

LVL 30

Assisted Solution

Subodh Tiwari (Neeraj) earned 100 total points
ID: 41831503
One way....

``````=IF(ISNUMBER(MATCH(A2&B2,{"19","212","318","415","522","695","1219","4252"},0)),"","OK")
``````
0

LVL 50

Accepted Solution

Rgonzo1971 earned 400 total points
ID: 41831509
Hi,

pls try
``````=IF(MAX((A2={1,2,3,4,5,6,12,42})+(B2={9,12,18,15,22,95,19,52}))=2,"","Ok")
``````
Regards
3

LVL 30

Assisted Solution

Subodh Tiwari (Neeraj) earned 100 total points
ID: 41831515
Rgonzo's formula can be simplified as below...

``````=IF(MAX((A2={1,2,3,4,5,6,12,42})*(B2={9,12,18,15,22,95,19,52})),"","Ok")
``````
0

LVL 30

Expert Comment

ID: 41831519
@Rgonzo
Good catch. Thanks for the information. :)
0

LVL 3

Author Closing Comment

ID: 41831546
WOW Rgonzo

I am really impressed.
1

LVL 26

Expert Comment

ID: 41831550
+1  Rgonzo1971
0

LVL 30

Expert Comment

ID: 41831560
Tweaked formula.....

``````=IF(ISNUMBER(MATCH(A2&" "&B2,{"1 9","2 12","3 18","4 15","5 22","6 95","12 19","42 52"},0)),"","OK")
``````
0

LVL 3

Author Comment

ID: 41831602
thanks Neeraj
0

LVL 30

Expert Comment

ID: 41831606
You're welcome!
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.