how to simplify this IF OR AND functions

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.
LVL 3
excelismagicAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
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")

Open in new window

Regards
3
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
One way....

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

Open in new window

0
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
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")

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Rgonzo
Good catch. Thanks for the information. :)
0
 
excelismagicAuthor Commented:
WOW Rgonzo

I am really impressed.
1
 
ProfessorJimJamCommented:
+1  Rgonzo1971
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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")

Open in new window

0
 
excelismagicAuthor Commented:
thanks Neeraj
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.