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?
 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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
All Courses

From novice to tech pro — start learning today.