Multi-Speadsheet information merge!

This is my problem:

Spreadsheet A: I have a spreadsheet with names, addresses, phone numbers, etc., but no zip codes.

Spreadsheet B: I have another spreadsheet with all the same information, plus the zip codes.

I need to somehow match the addresses of Spreadsheet A with the zip codes of Spreadsheet B.

Let me rephrase: What kind of formula can I put into my empty zip code cell that will match the address in Spreadsheet A with the address in Spreadsheet B, and pull the zip code information from Spreadsheet B into Spreadsheet A?

Thanks!

Hi

IF there is only 1 entry for each item, then you can use a combination of SUMPRODUCT and INDEX to return the postcode.

Say you have Name, Address, phone in columns A,B,C of sheet1 and Name, Address, Phone, Zip in columns A,B,C,D in sheet2, then the construct is

=INDEX(Sheet2!D:D,SUMPRODUCT(--(Sheet2!$A$2:$A$6=Sheet1!A2),--(Sheet2!$B$2:$B$6=Sheet1!B2),--(Sheet2!$C$2:$C$6=Sheet1!C2),ROW(Sheet2!$A$2:$A$6)))

If you need help to expand for your situation, then please give details of workbook names, sheet names, column details that match..... Perhaps an example workbook if it is really messy.

HTH

rylo

That was REALLY close!
It pulled zip codes... just not quite the right ones...

Let me explain the situation a bit further:

Sheet 2 is a list of about 19,000 rows of data.
The Name, Address, Phone Number, and Zip code are in rows A, B, C, and D.

Sheet 1 is a list of about 2000 rows of data.
The Name, Address, and Phone number are in columns A, B, and C.
I need a formula for column D to search for the corresponding address in Sheet 2 and plug in the appropriate zip code.

I really appreciate your help!
Thanks!

Hi

The construct of the formula should be the same, just need to extend the range to cover. What do you mean that

It pulled zip codes... just not quite the right ones

What was wrong with the ones it pulled?

How about attaching an example file so we can see the construct of your data.

rylo

For the confidentiality of our clients, the attached file has different names... But the format is identical.

Sheet 2 has the Names, Addresses, Phone Numbers, and Zip codes in columns A, B, C, and D.

Sheet 1 has the Names, Addresses, and Phone Numbers in columns A, B, and C.

I need a column in Sheet 1 that will find the appropriate zip code from Sheet 2 and put it in the cell.

Thanks!

Hi

Sheet1!D2: =IF(SUMPRODUCT(--(Sheet2!$A$2:$A$12=Sheet1!A2),--(Sheet2!$B$2:$B$12=Sheet1!B2),--(Sheet2!$C$2:$C$12=Sheet1!C2),ROW(Sheet2!$A$2:$A$12)),INDEX(Sheet2!D:D,SUMPRODUCT(--(Sheet2!$A$2:$A$12=Sheet1!A2),--(Sheet2!$B$2:$B$12=Sheet1!B2),--(Sheet2!$C$2:$C$12=Sheet1!C2),ROW(Sheet2!$A$2:$A$12))),"")

Copy down to D6.

rylo

really nice shortcut. but...if i cant type dublicate entries in A column? as ur instracations i typed in Sheet-1 d2==IF(SUMPRODUCT(--(Sheet2!$A$2:$A$12=Sheet1!A2),--(Sheet2!$B$2:$B$12=Sheet1!B2),--(Sheet2!$C$2:$C$12=Sheet1!C2),ROW(Sheet2!$A$2:$A$12)),INDEX(Sheet2!D:D,SUMPRODUCT(--(Sheet2!$A$2:$A$12=Sheet1!A2),--(Sheet2!$B$2:$B$12=Sheet1!B2),--(Sheet2!$C$2:$C$12=Sheet1!C2),ROW(Sheet2!$A$2:$A$12))),"")

i got result but when tried dublicate data in A column i didnt get output.

plz help me.

if u want file i will forward u.