OR function

[Chris_t_2k5]

I have an OR function in a nested if function as follows

OR(F2=A2,F2=B2,F2=C2,G2=A2,G2=B2,G2=C2,H2=A2,H2=B2,H2=C2.....

i.e. if any cells a2:c2 match any cells f2:h2

Is there any way to make this easier as will will be adding further to this.

Thanks.

[daddylonglegs]

One way....

=IF(SUMPRODUCT(1-ISNA(MATCH(A2:C2,F2:H2,0))),"match","no match")

[Bob Phillips]

You could use

ISNUMBER(MATCH(A2:C2,F2:H2,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Chris_t_2k5" <[email protected]> wrote in message
news:[email protected]...
> I have an OR function in a nested if function as follows
>
> OR(F2=A2,F2=B2,F2=C2,G2=A2,G2=B2,G2=C2,H2=A2,H2=B2,H2=C2.....
>
> i.e. if any cells a2:c2 match any cells f2:h2
>
> Is there any way to make this easier as will will be adding further to
this.
>
> Thanks.

[El Bee]

In cells A2:C2 "Larry", "Mike", "John"

In cells F2:H2 "Sallie", "Harry", "Mike"

use the following function:

=MATCH(A2:C2,F2:H2) the results will = 1


"Chris_t_2k5" wrote:

> I have an OR function in a nested if function as follows
>
> OR(F2=A2,F2=B2,F2=C2,G2=A2,G2=B2,G2=C2,H2=A2,H2=B2,H2=C2.....
>
> i.e. if any cells a2:c2 match any cells f2:h2
>
> Is there any way to make this easier as will will be adding further to this.
>
> Thanks.

[Kevin Vaughn]

This one works. Do you mind explaining how/why? Just to let you know, I
understand the if/then/else construct. I have successfully used sumproduct
(but not on something like this,) and I understand the use of ISNA. Also, I
had been trying to find a solution myself and had come up with the following,
which did not work:
=MATCH(A2:C2,F2:H2,0)
--
Kevin Vaughn


"daddylonglegs" wrote:

>
> One way....
>
> =IF(SUMPRODUCT(1-ISNA(MATCH(A2:C2,F2:H2,0))),"match","no match")
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=512906
>
>

[daddylonglegs]

Hi Kevin,

=MATCH(A2:C2,F2:H2,0)

as I'm sure you know, gives an array of either numbers (when there is a match) or #N/A (when there isn't) so something like

{#N/A,3,#N/A)

[if the only match is between B2 and H2]

=ISNA(MATCH(A2:C2,F2:H2,0))

would then convert this to

{TRUE,FALSE,TRUE)

and

=1-ISNA(MATCH(A2:C2,F2:H2,0))

gives you

{0,1,0}

SUMPRODUCT then adds these together so the SUMPRODUCT result is zero only when there are no matches

an alternative formula

=IF(SUMPRODUCT(COUNTIF(A2:C2,F2:H2)),"match","no match")

which I believe is less efficient but possibly useful if one of your ranges is not a single row or column, or even

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A2:C2,F2:H2,0))),"match","no match")

[Kevin Vaughn]

Thanks, that explanation was helpful. I didn't quite get the 1 - isna...
part, but now I understand.
--
Kevin Vaughn


"daddylonglegs" wrote:

>
> Hi Kevin,
>
> =MATCH(A2:C2,F2:H2,0)
>
> as I'm sure you know, gives an array of either numbers (when there is a
> match) or #N/A (when there isn't) so something like
>
> {#N/A,3,#N/A)
>
> [if the only match is between B2 and H2]
>
> =ISNA(MATCH(A2:C2,F2:H2,0))
>
> would then convert this to
>
> {TRUE,FALSE,TRUE)
>
> and
>
> =1-ISNA(MATCH(A2:C2,F2:H2,0))
>
> gives you
>
> {0,1,0}
>
> SUMPRODUCT then adds these together so the SUMPRODUCT result is zero
> only when there are no matches
>
> an alternative formula
>
> =IF(SUMPRODUCT(COUNTIF(A2:C2,F2:H2)),"match","no match")
>
> which I believe is less efficient but possibly useful if one of your
> ranges is not a single row or column, or even
>
> =IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A2:C2,F2:H2,0))),"match","no
> match")
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=512906
>
>