Compare two cells in two different files and return answer

[Doug]

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.

 

[Bernard Liengme]

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]

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

 

[Doug]

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

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

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.

.

 

[Doug]

I'm using the 2003 version?

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

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.

.

 

[David Biddulph]

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

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

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

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.

.

 

[Francis]

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