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)

 

[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

 

[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)

 

[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.