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.

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

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" wrote in message
...
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.

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.

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

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

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