Unnecessary or statement help needed

Posted by Bob on October 19, 2000 10:56 AM

I have a combo box on a worksheet that lists all of the possible combinations of the numbers 1-4 (not order specific) giving me 15 possible outcomes
ie: 1,2,3,4 - 1,2,3 - 2,3 etc.
There are 4 columns numbered (1,2,3,4) that take the value of the B column if the result contains that number through the following formula:
=IF(OR(\$A4="1,2,3,4",\$A4="1,2,3",\$A4="2",\$A4="1,2",\$A4="1,2,4",\$A4="2,3",\$A4="2,4",\$A4="2,3,4")=TRUE,\$B4,0)
This formula runs down the '2' column and returns B4 if A4 contains a '2'.
Is there any easier way to write this like "If A4 contains a '2' return B4"?
I don't see that Left or Right functions will work since the 2 (or other number) can be in any position.
Thanks for any help I can get.

Posted by Tim Francis-Wright on October 19, 2000 2:14 PM

Try
=IF(ISERR(FIND("2",\$A4)),0,\$B4)

The FIND function returns the location of
the first string in the second. If the first
string is not in the second, it returns a
#VALUE! error. So, the function above checks
for an error, returning 0 in that case and
the value from column B otherwise.

HTH