Comparing cells in workbooks

[Guest]

I would like to learn how to compare a row in Workbook1 against a range of
rows in Workbook2. If one of the rows in Workbook2 is the same as the row in
Workbook1, data from a cell in Workbook2 is then copied into an empty cell in
Workbook1. For example:

Workbook1 Columns
A=Name, B=Part, C=2005_Price

Workbook2 Columns
A=Name, B=Part, C=2004_Price

If a row in Workbook1 with columns A & B being exactly the same as ONE of
the many rows in Workbook2 (columns A & B), I would like to copy the info in
Workbook 2, 2004_Price, into column D of workbook1. Thus, when formula is
completed, Workbook1 should look like this:

Workbook1 Columns
A=Name, B=Part, C=2005_Price, D=2004_Price

I've tried VLookup and If, but can't get it to work. Your expert advice
please.

 

[Guest]

Does the data in column A and B match each other row for row 100% of the
time, or would this also need to be checked?

On the surface of it I'm thinking of 2 possible solutions, a formula array
or a simple macro to check the headers and copy and paste.

The formula array would probably be easiest try putting this in Workbbok 1
in cell D2 and copy down ....

=SUMPRODUCT(--([Workbook2]Sheet1!A2=A2), --([Workbook2]Sheet1!B2=B2,
([Workbook2]Sheet1!C2))

Something like this should work. It assumes that the data in Workbook2
exists in a sheet called Sheet1 though.

HTH.

 

[Guest]

Thanks for quick response, DaveO.

Data in Column A & B of Workbook1 must be check against Columns A & B of
Workbook 2. If the data is exactly the same, I'd like the data in Column C
of Workbook 2 to be copied to Column D of Workbook 1.

Every row of information is different.

Looking forward to your expert advice.

Short Coco

Does the data in column A and B match each other row for row 100% of the
time, or would this also need to be checked?

On the surface of it I'm thinking of 2 possible solutions, a formula array
or a simple macro to check the headers and copy and paste.

The formula array would probably be easiest try putting this in Workbbok 1
in cell D2 and copy down ....

=SUMPRODUCT(--([Workbook2]Sheet1!A2=A2), --([Workbook2]Sheet1!B2=B2,
([Workbook2]Sheet1!C2))

Something like this should work. It assumes that the data in Workbook2
exists in a sheet called Sheet1 though.

HTH.

I would like to learn how to compare a row in Workbook1 against a range of
rows in Workbook2. If one of the rows in Workbook2 is the same as the row in
Workbook1, data from a cell in Workbook2 is then copied into an empty cell in
Workbook1. For example:

Workbook1 Columns
A=Name, B=Part, C=2005_Price

Workbook2 Columns
A=Name, B=Part, C=2004_Price

If a row in Workbook1 with columns A & B being exactly the same as ONE of
the many rows in Workbook2 (columns A & B), I would like to copy the info in
Workbook 2, 2004_Price, into column D of workbook1. Thus, when formula is
completed, Workbook1 should look like this:

Workbook1 Columns
A=Name, B=Part, C=2005_Price, D=2004_Price

I've tried VLookup and If, but can't get it to work. Your expert advice
please.