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!

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" wrote:

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!

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$115 9)

Any ideas?

"JLatham" wrote:

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" wrote:

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!

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. " " is not the same as
" but it should not care about case " is same as
").

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" wrote:

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$115 9)

Any ideas?

"JLatham" wrote:

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" wrote:

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!

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

"JLatham" wrote:

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. " " is not the same as
" but it should not care about case " is same as
").

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" wrote:

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$115 9)

Any ideas?

"JLatham" wrote:

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" wrote:

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!