software

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))),quot;matchquot;,quot;no matchquot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=512906You 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)

quot;Chris_t_2k5quot; gt; wrote in message
...
gt; I have an OR function in a nested if function as follows
gt;
gt; OR(F2=A2,F2=B2,F2=C2,G2=A2,G2=B2,G2=C2,H2=A2,H2=B2 ,H2=C2.....
gt;
gt; i.e. if any cells a2:c2 match any cells f2:h2
gt;
gt; Is there any way to make this easier as will will be adding further to
this.
gt;
gt; Thanks.
In cells A2:C2 quot;Larryquot;, quot;Mikequot;, quot;Johnquot;

In cells F2:H2 quot;Salliequot;, quot;Harryquot;, quot;Mikequot;

use the following function:

=MATCH(A2:C2,F2:H2) the results will = 1quot;Chris_t_2k5quot; wrote:

gt; I have an OR function in a nested if function as follows
gt;
gt; OR(F2=A2,F2=B2,F2=C2,G2=A2,G2=B2,G2=C2,H2=A2,H2=B2 ,H2=C2.....
gt;
gt; i.e. if any cells a2:c2 match any cells f2:h2
gt;
gt; Is there any way to make this easier as will will be adding further to this.
gt;
gt; 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 Vaughnquot;daddylonglegsquot; wrote:

gt;
gt; One way....
gt;
gt; =IF(SUMPRODUCT(1-ISNA(MATCH(A2:C2,F2:H2,0))),quot;matchquot;,quot;no matchquot;)
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=512906
gt;
gt;


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)),quot;matchquot;,quot;no matchquot;)

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))),quot;matchquot;,quot;no
matchquot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=512906Thanks, that explanation was helpful. I didn't quite get the 1 - isna...
part, but now I understand.
--
Kevin Vaughnquot;daddylonglegsquot; wrote:

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