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

1. ## 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).  Register To Reply

2. ## Re: Help needed with a quicker way of referencing values in if-then formulas

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.`  Register To Reply

3. ## Re: Help needed with a quicker way of referencing values in if-then formulas 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.   Register To Reply

4. ## Re: Help needed with a quicker way of referencing values in if-then formulas

This is the "new" formula adapted from newdoverman:

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

5. ## Re: Help needed with a quicker way of referencing values in if-then formulas

You're welcome.

The only reason that B\$1 was in my formula was because you indicated in your original formula that it had some significance that you didn't explain.

Thanks for the feedback.  Register To Reply

6. ## Re: Help needed with a quicker way of referencing values in if-then formulas

Ok, got it!   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 