MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Complex AND OR Formula

• Follow

```HI

I need to express something in a formula , and am having trouble with
it.

I need to say this :

IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 ,
OTHERWISE PUT J2

Can someone assist with some code to make this happen , please?

Grateful for any help. Sorry for double post.

Best Wishes
```
 0

```=IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message
news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk...
>
> HI
>
> I need to express something in a formula , and am having trouble with it.
>
> I need to say this :
>
> IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 ,
> OTHERWISE PUT J2
>
> Can someone assist with some code to make this happen , please?
>
> Grateful for any help. Sorry for double post.
>
>
>
> Best Wishes

```
 0

```I assume that you intended =IF(AND(RIGHT(E2,2)="bx",j2=11),1,j2) ?

"bx", not "1bx", as you've got 2, not 3, as the second parameter of the
right function?
--
David Biddulph

"Don Guillett" <dguillett1@gmail.com> wrote in message
news:eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl...
> =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2)
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message
> news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk...
>>
>> HI
>>
>> I need to express something in a formula , and am having trouble with it.
>>
>> I need to say this :
>>
>> IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 ,
>> OTHERWISE PUT J2
>>
>> Can someone assist with some code to make this happen , please?
>>
>> Grateful for any help. Sorry for double post.
>>
>>
>>
>> Best Wishes
>

```
 0

```In article <eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl>, Don Guillett
<dguillett1@gmail.com> writes
>=IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2)
>

Hi Don

OK Thanks for that.

I can't quite get it to work though , for what I have in mind.

I found that this works for a single search parameter :

=IF(AND(COUNTIF(E2,"*1bx*"),COUNTIF(J2,"11")),1,J2)

but of course it only looks for "*1bx*". I need in incorporate an OR
expression to also search for "*2bx*" , "*3bx*" and "*4bx*" in the same
formula.

Any ideas how to work these other values in?

Best Wishes
```
 0

```In article <c5OdnVDaM7E0WZfRnZ2dnUVZ8uOdnZ2d@bt.com>, David Biddulph
<groups@[at]> writes
>I assume that you intended =IF(AND(RIGHT(E2,2)="bx",j2=11),1,j2) ?
>
>"bx", not "1bx", as you've got 2, not 3, as the second parameter of the
>right function?
>--
>David Biddulph

Hi David

Well no , not quite.

The formula would need to look specifically for any of the 4 phrases (
"*1bx*" OR "*2bx*" OR "*3bx*" OR "*4bx*") contained anywhere in E2.

If it finds any of these , and J2=11 , then put 1. If it doesn't find
any of these , put J2.

I would be dragging this down , so it would look in lower Cells in E
also.

There are other phrases containing the 'bx' suffix in E2 which I would
want it to ignore. So '5bx' , '6bx' would be ignored for example. For
this reason , it's not enough just to find 'bx'.

I wouldn't want the issued clouded by the fact that the expression
contains similar letters. The formula would need to identify them
discretely , in the same way as if it were looking for pink , blue ,
green or yellow.

>

>
>"Don Guillett" <dguillett1@gmail.com> wrote in message
>news:eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl...
>> =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2)
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett@gmail.com
>> "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message
>> news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk...
>>>
>>> HI
>>>
>>> I need to express something in a formula , and am having trouble with it.
>>>
>>> I need to say this :
>>>
>>> IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT
>1 ,
>>> OTHERWISE PUT J2
>>>
>>> Can someone assist with some code to make this happen , please?
>>>
>>> Grateful for any help. Sorry for double post.
>>>
>>>
>>>
>>> Best Wishes
>>
>

```
 0

```I did!!!

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:c5OdnVDaM7E0WZfRnZ2dnUVZ8uOdnZ2d@bt.com...
>I assume that you intended =IF(AND(RIGHT(E2,2)="bx",j2=11),1,j2) ?
>
> "bx", not "1bx", as you've got 2, not 3, as the second parameter of the
> right function?
> --
> David Biddulph
>
>
> "Don Guillett" <dguillett1@gmail.com> wrote in message
> news:eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl...
>> =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2)
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett@gmail.com
>> "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message
>> news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk...
>>>
>>> HI
>>>
>>> I need to express something in a formula , and am having trouble with
>>> it.
>>>
>>> I need to say this :
>>>
>>> IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 ,
>>> OTHERWISE PUT J2
>>>
>>> Can someone assist with some code to make this happen , please?
>>>
>>> Grateful for any help. Sorry for double post.
>>>
>>>
>>>
>>> Best Wishes
>>
>

```
 0

```>The formula would need to look specifically
>for any of the 4 phrases ("*1bx*" OR "*2bx*"
>OR "*3bx*" OR "*4bx*") contained anywhere in E2.

Try this...

=IF(COUNT(SEARCH({1,2,3,4}&"BX",E2))*(J2=11),1,J2)

--
Biff
Microsoft Excel MVP

"Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message
news:Gfx6WVAmlsCMFw6b@chayes.demon.co.uk...
> In article <c5OdnVDaM7E0WZfRnZ2dnUVZ8uOdnZ2d@bt.com>, David Biddulph
> <groups@[at]> writes
>>I assume that you intended =IF(AND(RIGHT(E2,2)="bx",j2=11),1,j2) ?
>>
>>"bx", not "1bx", as you've got 2, not 3, as the second parameter of the
>>right function?
>>--
>>David Biddulph
>
> Hi David
>
> Well no , not quite.
>
> The formula would need to look specifically for any of the 4 phrases (
> "*1bx*" OR "*2bx*" OR "*3bx*" OR "*4bx*") contained anywhere in E2.
>
> If it finds any of these , and J2=11 , then put 1. If it doesn't find any
> of these , put J2.
>
> I would be dragging this down , so it would look in lower Cells in E also.
>
> There are other phrases containing the 'bx' suffix in E2 which I would
> want it to ignore. So '5bx' , '6bx' would be ignored for example. For this
> reason , it's not enough just to find 'bx'.
>
> I wouldn't want the issued clouded by the fact that the expression
> contains similar letters. The formula would need to identify them
> discretely , in the same way as if it were looking for pink , blue , green
> or yellow.
>
>
>
>>
>
>
>>
>>"Don Guillett" <dguillett1@gmail.com> wrote in message
>>news:eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl...
>>> =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2)
>>>
>>> --
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> dguillett@gmail.com
>>> "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message
>>> news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk...
>>>>
>>>> HI
>>>>
>>>> I need to express something in a formula , and am having trouble with
>>>> it.
>>>>
>>>> I need to say this :
>>>>
>>>> IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT
>>1 ,
>>>> OTHERWISE PUT J2
>>>>
>>>> Can someone assist with some code to make this happen , please?
>>>>
>>>> Grateful for any help. Sorry for double post.
>>>>
>>>>
>>>>
>>>> Best Wishes
>>>
>>
>

```
 0

```On Sat, 5 Jun 2010 22:07:18 +0100, Colin Hayes wrote:
> I need to say this :
>
> IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 ,

Then why don't you read the numerous responses that were posted in
microsoft.public.excel.misc (including one from me)?

Please do not post the same question multiple times.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
Shikata ga nai...
```
 0

```In article <OKauixRBLHA.5464@TK2MSFTNGP05.phx.gbl>, T. Valko
<biffinpitt@comcast.net> writes
>>The formula would need to look specifically
>>for any of the 4 phrases ("*1bx*" OR "*2bx*"
>>OR "*3bx*" OR "*4bx*") contained anywhere in E2.
>
>Try this...
>
>=IF(COUNT(SEARCH({1,2,3,4}&"BX",E2))*(J2=11),1,J2)
>

HI Biff

Yes , that's got it. Perfect first time.

Thanks for your time and expertise.

Best Wishes
```
 0

```You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message
news:p4LB\$bANJ7CMFwMj@chayes.demon.co.uk...
> In article <OKauixRBLHA.5464@TK2MSFTNGP05.phx.gbl>, T. Valko
> <biffinpitt@comcast.net> writes
>>>The formula would need to look specifically
>>>for any of the 4 phrases ("*1bx*" OR "*2bx*"
>>>OR "*3bx*" OR "*4bx*") contained anywhere in E2.
>>
>>Try this...
>>
>>=IF(COUNT(SEARCH({1,2,3,4}&"BX",E2))*(J2=11),1,J2)
>>
>
>
> HI Biff
>
> Yes , that's got it. Perfect first time.
>
> Thanks for your time and expertise.
>
>
>
> Best Wishes

```
 0

9 Replies
459 Views

Similiar Articles:

8/1/2012 6:32:11 AM