Help needed with a quicker way of referencing values in if-then formulas

I have a formula that looks like this:

=if(OR(B$1=2,B$1=5,B$1=33,B$1="b",B$1="m",B$1="z"),"Ok","Check Value").

In reality, however, instead of having only 6 reference values (2, 5, 33, b, m,and z), I actually have a hundred of these non-changing reference values and 1,000 data to check/compare.

Is there a way of collecting all of these reference values in one cell (for example, in A1) and have a simple formula reference A1 while satisfying the conditions? If not possible, is there any other simpler method than having using the OR function with 100 different reference values (I have not tried if this is possible or if there is a limit in OR functions).

Thanks in advance.

Originally Posted by newdoverman

Create a list of values that you want to compare with B1. I made a list in column M then enter this formula where you want the result:

Formula:

Please Login or Register  to view this content.

Thanks newdoverman. Your formula worked for me. However, I had to remove the $ sign in B$1 because I needed to copy the formula down. Thanks again for your help.

This is the "new" formula adapted from newdoverman:

=IF(COUNTIF($M$1:$M$6,B1)=1,"OK","Check Value")

Ok, got it!