Appending Data from one spreadsheet to another

[GeorgeA]

I have 2 spreadsheets with different data but they both have a column for
email address. I need to compare the email addresses in both spreadsheets and
when a match is found, copy and paste the data from columns A,B & C in
spreadsheet 1 into columns X,Y & Z in spreadsheet 2.
End result should be a complete row of data in one spreadsheet only.
Is this possible in Excel?

Thanks in advance!

 

[JLatham]

You can do this with the LOOKUP() function.

Your Sheet1 has information in columns A, B and C that needs to be copied to
Sheet2, columns X, Y and Z based on a match of email addresses on both sheets.

Assume Sheet1 list of information goes from row 1 to row 100, and it has
email addresses in column G.
Assume Sheet2 list of information begins on row 2 and the email addresses
are in column F.

On Sheet2, in X2, enter a formula like this (change sheet name Sheet1 to the
same as your real sheet)
=LOOKUP($F2,Sheet1!$G$1:$G$100,Sheet1!A$1:A$100)
drag/fill that formula on over into columns Y and Z it will change to:
(in Y2) =LOOKUP($F2,Sheet1!$G$1:$G$100,Sheet1!B$1:B$100)
(in Z2) =LOOKUP($F2,Sheet1!$G$1:$G$100,Sheet1!C$1:C$100)

Now drag/fill those formulas on down sheet 2 as far as they need to go.

If you want to make the changes permanent so you can delete sheet1, you can
select all used cells in columns X, Y and Z and use Edit-->Copy followed
immediately (without unselecting the cells) with Edit-->Paste Special with
the "Values" option selected.

Hope this helps. And hope the system doesn't reject my reply again!

 

[GeorgeA]

Thanks for your reply. It seems like it should work but it keeps returning
#N/A.

I'm using in the spreadsheet where I want to paste the data from Sheet 1.
My email addresses are in column G.
In Sheet 1 the email addresses are in column F and I want to bring in the
value of column A:

=LOOKUP($G2,Sheet1!$F$2:$F$1159,Sheet1!$A$2:$A$1159)

Any ideas?

 

[JLatham]

Your formula looks like it should work: taking the value on the same sheet
with the formula and using the entry in G2 to look in F2:F1159 on Sheet1 to
return corresponding entry from column A on that sheet (Sheet1).

Make sure you have the sheet name correct? Have you double-checked to make
sure that the entry in G2 is actually in the list from F2:F1159 on the other
sheet?

#N/A means that the value to be matched (G2) cannot be found in the lookup
array (F2:F1159). Leading and trailing blanks can be a problem, and any
minor misspelling will result in a miss. " (e-mail address removed)" is not the same as
"(e-mail address removed)" but it should not care about case ("(e-mail address removed)" is same as
"(e-mail address removed)").

If you want, you could send the sheet as an attachment to email to me at
(remove spaces)
HelpFrom @ JLathamsite.com
and I'll try to figure it out.

 

[GeorgeA]

Thanks very much for your help. I ended up using an INDEX formula and it works.