I am attempting to compare two text cells and if they both exist, I want to
bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an
example of text.

File 1
Col A Col B
BRATTA ZPB_COMMON
AHERNC ZPB_COMMON
GENCABH ZPB_COMMON

File 2
Col A Col B
BRATTA ZPB_COMMON
AHERNC ZPB_COMMON
GENCABH

So, what I want to do is say in File 1 take cells A1 and B1 compare them to
File 2 and search Col A and Col B and if there is an exact match somewhere
in file 2 then return a "yes" in column C. If not, "no" in column C.

So, file 2 would look like this:
File 2
Col A Col B Col C
BRATTA ZPB_COMMON Yes
AHERNC ZPB_COMMON Yes
GENCABH No


Thanks in advance for any help.

In C1 of Sheet2
=IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!$A$1:$A$20=A1),--('[My First
File.xls]Sheet1'!$B$1:$B$20=B1)),"Yes","No")
Replace the two '20' entries by however number of rows your need to compare
Copy down the column
Only in Excel 2007 can you use full column references as in
=IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!A:A=A1),--('[My First
File.xls]Sheet1'!B:B=B1)),"Yes","No")
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Doug" <(E-Mail Removed)> wrote in message
news:A7F2CF7B-930C-44DC-9B2F-(E-Mail Removed)...
> I am attempting to compare two text cells and if they both exist, I want
> to
> bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an
> example of text.
>
> File 1
> Col A Col B
> BRATTA ZPB_COMMON
> AHERNC ZPB_COMMON
> GENCABH ZPB_COMMON
>
> File 2
> Col A Col B
> BRATTA ZPB_COMMON
> AHERNC ZPB_COMMON
> GENCABH
>
> So, what I want to do is say in File 1 take cells A1 and B1 compare them
> to
> File 2 and search Col A and Col B and if there is an exact match
> somewhere
> in file 2 then return a "yes" in column C. If not, "no" in column C.
>
> So, file 2 would look like this:
> File 2
> Col A Col B Col C
> BRATTA ZPB_COMMON Yes
> AHERNC ZPB_COMMON Yes
> GENCABH No
>
>
> Thanks in advance for any help.
>
>

If you are using Excel 2007
=IF(COUNTIFS('[My First File.xls]Sheet1'!$A:$A,A1,'[My First
File.xls]Sheet1'!$B:$B,B1),"y","n")
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Doug" <(E-Mail Removed)> wrote in message
news:A7F2CF7B-930C-44DC-9B2F-(E-Mail Removed)...
> I am attempting to compare two text cells and if they both exist, I want
> to
> bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an
> example of text.
>
> File 1
> Col A Col B
> BRATTA ZPB_COMMON
> AHERNC ZPB_COMMON
> GENCABH ZPB_COMMON
>
> File 2
> Col A Col B
> BRATTA ZPB_COMMON
> AHERNC ZPB_COMMON
> GENCABH
>
> So, what I want to do is say in File 1 take cells A1 and B1 compare them
> to
> File 2 and search Col A and Col B and if there is an exact match
> somewhere
> in file 2 then return a "yes" in column C. If not, "no" in column C.
>
> So, file 2 would look like this:
> File 2
> Col A Col B Col C
> BRATTA ZPB_COMMON Yes
> AHERNC ZPB_COMMON Yes
> GENCABH No
>
>
> Thanks in advance for any help.
>
>

Thanks Bernard.

But, I'm still having an issue. I get a #value error for my return. Here is
the formula as a wrote it.

=IF(SUMPRODUCT(--($C$2:$I$259=C2),--('New Composites.xls'!$A$2:$B$4707='New
Composites.xls'!$A$2)),"Yes","no")

Any help is appreciated. Thanks again

"Bernard Liengme" wrote:

> In C1 of Sheet2
> =IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!$A$1:$A$20=A1),--('[My First
> File.xls]Sheet1'!$B$1:$B$20=B1)),"Yes","No")
> Replace the two '20' entries by however number of rows your need to compare
> Copy down the column
> Only in Excel 2007 can you use full column references as in
> =IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!A:A=A1),--('[My First
> File.xls]Sheet1'!B:B=B1)),"Yes","No")
> best wishes
> --
> Bernard Liengme
> http://people.stfx.ca/bliengme
> Microsoft Excel MVP
>
> "Doug" <(E-Mail Removed)> wrote in message
> news:A7F2CF7B-930C-44DC-9B2F-(E-Mail Removed)...
> > I am attempting to compare two text cells and if they both exist, I want
> > to
> > bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an
> > example of text.
> >
> > File 1
> > Col A Col B
> > BRATTA ZPB_COMMON
> > AHERNC ZPB_COMMON
> > GENCABH ZPB_COMMON
> >
> > File 2
> > Col A Col B
> > BRATTA ZPB_COMMON
> > AHERNC ZPB_COMMON
> > GENCABH
> >
> > So, what I want to do is say in File 1 take cells A1 and B1 compare them
> > to
> > File 2 and search Col A and Col B and if there is an exact match
> > somewhere
> > in file 2 then return a "yes" in column C. If not, "no" in column C.
> >
> > So, file 2 would look like this:
> > File 2
> > Col A Col B Col C
> > BRATTA ZPB_COMMON Yes
> > AHERNC ZPB_COMMON Yes
> > GENCABH No
> >
> >
> > Thanks in advance for any help.
> >
> >
> .
>

I'm using the 2003 version?


"Bernard Liengme" wrote:

> If you are using Excel 2007
> =IF(COUNTIFS('[My First File.xls]Sheet1'!$A:$A,A1,'[My First
> File.xls]Sheet1'!$B:$B,B1),"y","n")
> best wishes
> --
> Bernard Liengme
> http://people.stfx.ca/bliengme
> Microsoft Excel MVP
>
> "Doug" <(E-Mail Removed)> wrote in message
> news:A7F2CF7B-930C-44DC-9B2F-(E-Mail Removed)...
> > I am attempting to compare two text cells and if they both exist, I want
> > to
> > bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an
> > example of text.
> >
> > File 1
> > Col A Col B
> > BRATTA ZPB_COMMON
> > AHERNC ZPB_COMMON
> > GENCABH ZPB_COMMON
> >
> > File 2
> > Col A Col B
> > BRATTA ZPB_COMMON
> > AHERNC ZPB_COMMON
> > GENCABH
> >
> > So, what I want to do is say in File 1 take cells A1 and B1 compare them
> > to
> > File 2 and search Col A and Col B and if there is an exact match
> > somewhere
> > in file 2 then return a "yes" in column C. If not, "no" in column C.
> >
> > So, file 2 would look like this:
> > File 2
> > Col A Col B Col C
> > BRATTA ZPB_COMMON Yes
> > AHERNC ZPB_COMMON Yes
> > GENCABH No
> >
> >
> > Thanks in advance for any help.
> >
> >
> .
>

Your ranges need to be the same length.
259<>4707
--
David Biddulph

"Doug" <(E-Mail Removed)> wrote in message
news:BD24ECE4-3EF4-41DB-8412-(E-Mail Removed)...
> Thanks Bernard.
>
> But, I'm still having an issue. I get a #value error for my return. Here
> is
> the formula as a wrote it.
>
> =IF(SUMPRODUCT(--($C$2:$I$259=C2),--('New
> Composites.xls'!$A$2:$B$4707='New
> Composites.xls'!$A$2)),"Yes","no")
>
> Any help is appreciated. Thanks again
>
> "Bernard Liengme" wrote:
>
>> In C1 of Sheet2
>> =IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!$A$1:$A$20=A1),--('[My
>> First
>> File.xls]Sheet1'!$B$1:$B$20=B1)),"Yes","No")
>> Replace the two '20' entries by however number of rows your need to
>> compare
>> Copy down the column
>> Only in Excel 2007 can you use full column references as in
>> =IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!A:A=A1),--('[My First
>> File.xls]Sheet1'!B:B=B1)),"Yes","No")
>> best wishes
>> --
>> Bernard Liengme
>> http://people.stfx.ca/bliengme
>> Microsoft Excel MVP
>>
>> "Doug" <(E-Mail Removed)> wrote in message
>> news:A7F2CF7B-930C-44DC-9B2F-(E-Mail Removed)...
>> > I am attempting to compare two text cells and if they both exist, I
>> > want
>> > to
>> > bring in a "yes" answer. If they don't, bring in a "no" answer. Here's
>> > an
>> > example of text.
>> >
>> > File 1
>> > Col A Col B
>> > BRATTA ZPB_COMMON
>> > AHERNC ZPB_COMMON
>> > GENCABH ZPB_COMMON
>> >
>> > File 2
>> > Col A Col B
>> > BRATTA ZPB_COMMON
>> > AHERNC ZPB_COMMON
>> > GENCABH
>> >
>> > So, what I want to do is say in File 1 take cells A1 and B1 compare
>> > them
>> > to
>> > File 2 and search Col A and Col B and if there is an exact match
>> > somewhere
>> > in file 2 then return a "yes" in column C. If not, "no" in column C.
>> >
>> > So, file 2 would look like this:
>> > File 2
>> > Col A Col B Col C
>> > BRATTA ZPB_COMMON Yes
>> > AHERNC ZPB_COMMON Yes
>> > GENCABH No
>> >
>> >
>> > Thanks in advance for any help.
>> >
>> >
>> .
>>

Hi
Try this

=IF(SUMPRODUCT(--([Book1]Sheet1!$A$2:$A$4=A2),--([Book1]Sheet1!$B$2:$B$4=B2)),"Yes","No")

Bear in mind that your range need to be the same,eg the range A2 to A4 in
Sheet1 of Book1 must be the same in Sheet1 of Book2, ie A2 to A4, otherwise
you will get error message
--
Hope this help

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis





"Doug" wrote:

> I am attempting to compare two text cells and if they both exist, I want to
> bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an
> example of text.
>
> File 1
> Col A Col B
> BRATTA ZPB_COMMON
> AHERNC ZPB_COMMON
> GENCABH ZPB_COMMON
>
> File 2
> Col A Col B
> BRATTA ZPB_COMMON
> AHERNC ZPB_COMMON
> GENCABH
>
> So, what I want to do is say in File 1 take cells A1 and B1 compare them to
> File 2 and search Col A and Col B and if there is an exact match somewhere
> in file 2 then return a "yes" in column C. If not, "no" in column C.
>
> So, file 2 would look like this:
> File 2
> Col A Col B Col C
> BRATTA ZPB_COMMON Yes
> AHERNC ZPB_COMMON Yes
> GENCABH No
>
>
> Thanks in advance for any help.
>
>