How to tell microsoft that Excel Union Operator doesn't always work

[UncleBob]

What is the best way to report a bug to microsoft?

Cell A1 through F1 contain the colors red, blue,
green, yellow, pink, purple

Cell a2 contains green

Cell b2 contains =or(a2=(a1:f1)) as a control
shift enter array formula.

Cell c2 contains =or(a2 = (a1:b1,d1:f1)) CSE

Cell b2 works fine, but cell c2 does not work.
Seems to me that the Union operator (which is a ,)
should work but does not.

 

[wie.niet]

What is the best way to report a bug to microsoft?

Cell A1 through F1 contain the colors red, blue,
green, yellow, pink, purple

Cell a2 contains green

Cell b2 contains =or(a2=(a1:f1)) as a control
shift enter array formula.

Cell c2 contains =or(a2 = (a1:b1,d1:f1)) CSE

Cell b2 works fine, but cell c2 does not work.
Seems to me that the Union operator (which is a ,)
should work but does not.

you should use:
=OR(A1=(A1:B1);A1=(D1:F1)) CSE
i think its somewhere in the help files, but dont ask me where......

 

[Jerry W. Lewis]

Where did you get the idea that this should work? Select A3:F3 and
array enter
=a2=(a1:f1)
to see why =OR(a2=(a1:f1)) works. Then array enter
=a2=(a1:b1,d1:f1)
to see why it doesn't.

You are probably thinking of an array constant (see Help for "About
array formulas and array constants"), but the syntax uses {} instead of
(), and only involves constatnts, not ranges.

wie.niet suggested a workaround. Depending on your regional settings,
you may need to replace the ; with , in that suggestion.

Jerry
Excel MVP

 

[UncleBob]

"UncleBob" <[email protected]> schreef in bericht

you should use:
=OR(A1=(A1:B1);A1=(D1:F1)) CSE
i think its somewhere in the help files, but dont ask me where......

Thank you for the workaround [In my region it is
=OR(A1=(A1:B1),A1=(D1:F1)) CSE].

I still feel there is a bug with the union
operator. if or(a5:b5,d1:f1) works, why doesn't
or(a1=(a1:b1,d1:f1)?
But, I suppose you can't fight city hall, so I am
grateful for your help and will close this issue.