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
