help with vlookup to merge two lists

[dlee388]

Can someone please help me with using vlookup to merge two lists based on a
common email address. I want to match email addresses between the two
spreadsheets and bring the data from the second spreadsheet to the matching
record (email address) on the first spreadsheet. I've read all sorts of
online articles and read help, but still can't understand how to get vlookup
to work correctly.

Spreadsheet 1 is a master list with contact information for 400 people
including their email addresses in column R. Spreadsheet 2 is a second list
of 200 names with their email address in column E and the Response in column
F (column 6). I want to exact match records on email address and copy the
Response in column F, spreadsheet 2 to the corresponding email address row in
the new column on spreadsheet 1. The lists have header rows.

I created a new column in Spreadsheet 1.
Then in Row 2 in that new column, I created:
vlookup(R2, (spreadsheet 2 table range to look for matching email address),
(spreadsheet 2 column to grab the Response), False)

* Vlookup is located in cell W2
* R2 in spreadsheet 1 is the cell with the corresponding email address
* spreadsheet 2 column to grab the Response: I tried using 6 for the column
F in spreadsheet two where Response is located. I also tried highlighting
the entire column 6 in spreadsheet 2.
* False for exact match

I can't get vlookup to work properly. I hope that my explanation is clear.
I would greatly appreciate any help.

 

[Conan Kelly]

dlee388,

in your example, "(spreadsheet 2 table range to look for matching email
address)" should be a range that is 2 columns wide by 200 + rows tall (make
sure your range addresses have "$" in them in case you need to copy the
formula down/over so it will refer to the same table range).

In order for the vlookup function to work, you need to give the column
number of the table range......not the column number of the physical column
on the sheet. In your case, this will be column 2 (second column of the
table range), not column 6 (physical column on the sheet).

HTH,

Conan Kelly

 

[dlee388]

Sorry, but I still can't figure it out. Is there something else that I'm
missing? Here's the formula that I tried based on what you suggested:

=VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)

- S2 is the cell in spreadsheet # 1 that contains the email address and I
want to find that exact email record in spreadsheet #2.

- [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260
is the table array referencing spreadsheet # 2 that contains the Reference
information that I want to bring into spreadsheet #1. I highlighted all the
data in the 2 column spreadsheet #2.

- Spreadsheet #2 is a two column spreadsheet that contains email address in
column #1 and the Reference information in column #2.

- I removed the hyperlinks from email addresses in both spreadsheets and
visually confirmed that there are no leading spaces.

 

[Conan Kelly]

dlee388,

What results are you getting? Have you copied this formula all the way
down?

If there is not a match in spreadsheet #2, the the formula will return an
n/a error ("#N/A"). If that is what you are getting, you could modify the
formula to this:

=if(isna(VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)),"",VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE))

Doing this, the formula will make the cell APPEAR blank if there is no match
and will return the results if there is a match.

HTH,

Conan

Sorry, but I still can't figure it out. Is there something else that I'm
missing? Here's the formula that I tried based on what you suggested:

=VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)

- S2 is the cell in spreadsheet # 1 that contains the email address and I
want to find that exact email record in spreadsheet #2.

- [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260
is the table array referencing spreadsheet # 2 that contains the Reference
information that I want to bring into spreadsheet #1. I highlighted all
the
data in the 2 column spreadsheet #2.

- Spreadsheet #2 is a two column spreadsheet that contains email address
in
column #1 and the Reference information in column #2.

- I removed the hyperlinks from email addresses in both spreadsheets and
visually confirmed that there are no leading spaces.

dlee388,

in your example, "(spreadsheet 2 table range to look for matching email
address)" should be a range that is 2 columns wide by 200 + rows tall
(make
sure your range addresses have "$" in them in case you need to copy the
formula down/over so it will refer to the same table range).

In order for the vlookup function to work, you need to give the column
number of the table range......not the column number of the physical
column
on the sheet. In your case, this will be column 2 (second column of the
table range), not column 6 (physical column on the sheet).

HTH,

Conan Kelly

 

[dlee388]

New information: On spreadsheet #2, there appears to be a trailing space at
the end of the email address and Excel is not matching as a result of that.
I copied all of the email addresses into spreadsheet #1 and then used the IF
function to determine if Excel was seeing the matches on the email address.
It was not seeing the matches because of that trailing space.

I looked closer at the formatting on spreadsheet #2 and the cells had
different alignment formatting. So I turned off wrap text and set alignment
the same on both spreadsheets.

I tried using the trim function to remove the trailing space, but it was not
successful. I also tried using Replace to get rid of the trailing space, but
the Replace function couldn't see the trailing space. However, when I click
my mouse on each cell, the trailing space is definitely there and when I hit
backspace to remove the trailing space, then Excel sees the email address as
being a match. So perhaps now my problem has shifted to trying to figure out
how to remove this trailing space before I can get vlookup to work properly.

Sorry, but I still can't figure it out. Is there something else that I'm
missing? Here's the formula that I tried based on what you suggested:

=VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)

- S2 is the cell in spreadsheet # 1 that contains the email address and I
want to find that exact email record in spreadsheet #2.

- [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260
is the table array referencing spreadsheet # 2 that contains the Reference
information that I want to bring into spreadsheet #1. I highlighted all the
data in the 2 column spreadsheet #2.

- Spreadsheet #2 is a two column spreadsheet that contains email address in
column #1 and the Reference information in column #2.

- I removed the hyperlinks from email addresses in both spreadsheets and
visually confirmed that there are no leading spaces.

dlee388,

in your example, "(spreadsheet 2 table range to look for matching email
address)" should be a range that is 2 columns wide by 200 + rows tall (make
sure your range addresses have "$" in them in case you need to copy the
formula down/over so it will refer to the same table range).

In order for the vlookup function to work, you need to give the column
number of the table range......not the column number of the physical column
on the sheet. In your case, this will be column 2 (second column of the
table range), not column 6 (physical column on the sheet).

HTH,

Conan Kelly

 

[Conan Kelly]

dlee388,

The trailing space is on spreadsheet #2?

(assuming your data starts in A2)
In an unused column (column C for example), enter this formula in C2:

=trim(A2)

Copy that down to the bottom of your data.

Now column C will have the data in column A, but trimmed of leading/trailing
spaces. You can leave this column there and adjust your formulas to refer
to this column
OR
you can copy column C and Paste as values (right-click>Paste
Special...>Values) over the top of column A, then delete column C. Then
your VLOOKUP's should work without adjustments.

HTH,

Conan

New information: On spreadsheet #2, there appears to be a trailing space
at
the end of the email address and Excel is not matching as a result of
that.
I copied all of the email addresses into spreadsheet #1 and then used the
IF
function to determine if Excel was seeing the matches on the email
address.
It was not seeing the matches because of that trailing space.

I looked closer at the formatting on spreadsheet #2 and the cells had
different alignment formatting. So I turned off wrap text and set
alignment
the same on both spreadsheets.

I tried using the trim function to remove the trailing space, but it was
not
successful. I also tried using Replace to get rid of the trailing space,
but
the Replace function couldn't see the trailing space. However, when I
click
my mouse on each cell, the trailing space is definitely there and when I
hit
backspace to remove the trailing space, then Excel sees the email address
as
being a match. So perhaps now my problem has shifted to trying to figure
out
how to remove this trailing space before I can get vlookup to work
properly.

Sorry, but I still can't figure it out. Is there something else that I'm
missing? Here's the formula that I tried based on what you suggested:

=VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)

- S2 is the cell in spreadsheet # 1 that contains the email address and I
want to find that exact email record in spreadsheet #2.

- [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260
is the table array referencing spreadsheet # 2 that contains the
Reference
information that I want to bring into spreadsheet #1. I highlighted all
the
data in the 2 column spreadsheet #2.

- Spreadsheet #2 is a two column spreadsheet that contains email address
in
column #1 and the Reference information in column #2.

- I removed the hyperlinks from email addresses in both spreadsheets and
visually confirmed that there are no leading spaces.

dlee388,

in your example, "(spreadsheet 2 table range to look for matching email
address)" should be a range that is 2 columns wide by 200 + rows tall
(make
sure your range addresses have "$" in them in case you need to copy the
formula down/over so it will refer to the same table range).

In order for the vlookup function to work, you need to give the column
number of the table range......not the column number of the physical
column
on the sheet. In your case, this will be column 2 (second column of
the
table range), not column 6 (physical column on the sheet).

HTH,

Conan Kelly




Can someone please help me with using vlookup to merge two lists
based on
a
common email address. I want to match email addresses between the
two
spreadsheets and bring the data from the second spreadsheet to the
matching
record (email address) on the first spreadsheet. I've read all sorts
of
online articles and read help, but still can't understand how to get
vlookup
to work correctly.

Spreadsheet 1 is a master list with contact information for 400
people
including their email addresses in column R. Spreadsheet 2 is a
second
list
of 200 names with their email address in column E and the Response in
column
F (column 6). I want to exact match records on email address and
copy the
Response in column F, spreadsheet 2 to the corresponding email
address row
in
the new column on spreadsheet 1. The lists have header rows.

I created a new column in Spreadsheet 1.
Then in Row 2 in that new column, I created:
vlookup(R2, (spreadsheet 2 table range to look for matching email
address),
(spreadsheet 2 column to grab the Response), False)

* Vlookup is located in cell W2
* R2 in spreadsheet 1 is the cell with the corresponding email
address
* spreadsheet 2 column to grab the Response: I tried using 6 for the
column
F in spreadsheet two where Response is located. I also tried
highlighting
the entire column 6 in spreadsheet 2.
* False for exact match

I can't get vlookup to work properly. I hope that my explanation is
clear.
I would greatly appreciate any help.

 

[dlee388]

SUCCESS - FINALLY. It only took all afternoon. Turns out there was a
stubborn trailing space at the end of every email address in spreadsheet #2.
I had to use the following to remove that trailing space:
=TRIM(SUBSTITUTE(C20,CHAR(160)," "))

Once the trailing space was removed, I was able to get Vlookup to work based
on the information that you (Conan) gave me.

Thanks,
Dani

New information: On spreadsheet #2, there appears to be a trailing space at
the end of the email address and Excel is not matching as a result of that.
I copied all of the email addresses into spreadsheet #1 and then used the IF
function to determine if Excel was seeing the matches on the email address.
It was not seeing the matches because of that trailing space.

I looked closer at the formatting on spreadsheet #2 and the cells had
different alignment formatting. So I turned off wrap text and set alignment
the same on both spreadsheets.

I tried using the trim function to remove the trailing space, but it was not
successful. I also tried using Replace to get rid of the trailing space, but
the Replace function couldn't see the trailing space. However, when I click
my mouse on each cell, the trailing space is definitely there and when I hit
backspace to remove the trailing space, then Excel sees the email address as
being a match. So perhaps now my problem has shifted to trying to figure out
how to remove this trailing space before I can get vlookup to work properly.

Sorry, but I still can't figure it out. Is there something else that I'm
missing? Here's the formula that I tried based on what you suggested:

=VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)

- S2 is the cell in spreadsheet # 1 that contains the email address and I
want to find that exact email record in spreadsheet #2.

- [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260
is the table array referencing spreadsheet # 2 that contains the Reference
information that I want to bring into spreadsheet #1. I highlighted all the
data in the 2 column spreadsheet #2.

- Spreadsheet #2 is a two column spreadsheet that contains email address in
column #1 and the Reference information in column #2.

- I removed the hyperlinks from email addresses in both spreadsheets and
visually confirmed that there are no leading spaces.

dlee388,

in your example, "(spreadsheet 2 table range to look for matching email
address)" should be a range that is 2 columns wide by 200 + rows tall (make
sure your range addresses have "$" in them in case you need to copy the
formula down/over so it will refer to the same table range).

In order for the vlookup function to work, you need to give the column
number of the table range......not the column number of the physical column
on the sheet. In your case, this will be column 2 (second column of the
table range), not column 6 (physical column on the sheet).

HTH,

Conan Kelly




Can someone please help me with using vlookup to merge two lists based on
a
common email address. I want to match email addresses between the two
spreadsheets and bring the data from the second spreadsheet to the
matching
record (email address) on the first spreadsheet. I've read all sorts of
online articles and read help, but still can't understand how to get
vlookup
to work correctly.

Spreadsheet 1 is a master list with contact information for 400 people
including their email addresses in column R. Spreadsheet 2 is a second
list
of 200 names with their email address in column E and the Response in
column
F (column 6). I want to exact match records on email address and copy the
Response in column F, spreadsheet 2 to the corresponding email address row
in
the new column on spreadsheet 1. The lists have header rows.

I created a new column in Spreadsheet 1.
Then in Row 2 in that new column, I created:
vlookup(R2, (spreadsheet 2 table range to look for matching email
address),
(spreadsheet 2 column to grab the Response), False)

* Vlookup is located in cell W2
* R2 in spreadsheet 1 is the cell with the corresponding email address
* spreadsheet 2 column to grab the Response: I tried using 6 for the
column
F in spreadsheet two where Response is located. I also tried highlighting
the entire column 6 in spreadsheet 2.
* False for exact match

I can't get vlookup to work properly. I hope that my explanation is
clear.
I would greatly appreciate any help.

 

[Conan Kelly]

dlee388,

I'm glad everything worked out.

I'm not sure why =TRIM() did not work for you. It should have.

Check out my response to your last post for more info/different ways of
accomplishing the same thing.

Conan

SUCCESS - FINALLY. It only took all afternoon. Turns out there was a
stubborn trailing space at the end of every email address in spreadsheet
#2.
I had to use the following to remove that trailing space:
=TRIM(SUBSTITUTE(C20,CHAR(160)," "))

Once the trailing space was removed, I was able to get Vlookup to work
based
on the information that you (Conan) gave me.

Thanks,
Dani

New information: On spreadsheet #2, there appears to be a trailing space
at
the end of the email address and Excel is not matching as a result of
that.
I copied all of the email addresses into spreadsheet #1 and then used the
IF
function to determine if Excel was seeing the matches on the email
address.
It was not seeing the matches because of that trailing space.

I looked closer at the formatting on spreadsheet #2 and the cells had
different alignment formatting. So I turned off wrap text and set
alignment
the same on both spreadsheets.

I tried using the trim function to remove the trailing space, but it was
not
successful. I also tried using Replace to get rid of the trailing space,
but
the Replace function couldn't see the trailing space. However, when I
click
my mouse on each cell, the trailing space is definitely there and when I
hit
backspace to remove the trailing space, then Excel sees the email address
as
being a match. So perhaps now my problem has shifted to trying to figure
out
how to remove this trailing space before I can get vlookup to work
properly.

Sorry, but I still can't figure it out. Is there something else that
I'm
missing? Here's the formula that I tried based on what you suggested:

=VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)

- S2 is the cell in spreadsheet # 1 that contains the email address and
I
want to find that exact email record in spreadsheet #2.

-
[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260
is the table array referencing spreadsheet # 2 that contains the
Reference
information that I want to bring into spreadsheet #1. I highlighted
all the
data in the 2 column spreadsheet #2.

- Spreadsheet #2 is a two column spreadsheet that contains email
address in
column #1 and the Reference information in column #2.

- I removed the hyperlinks from email addresses in both spreadsheets
and
visually confirmed that there are no leading spaces.



:

dlee388,

in your example, "(spreadsheet 2 table range to look for matching
email
address)" should be a range that is 2 columns wide by 200 + rows tall
(make
sure your range addresses have "$" in them in case you need to copy
the
formula down/over so it will refer to the same table range).

In order for the vlookup function to work, you need to give the
column
number of the table range......not the column number of the physical
column
on the sheet. In your case, this will be column 2 (second column of
the
table range), not column 6 (physical column on the sheet).

HTH,

Conan Kelly




Can someone please help me with using vlookup to merge two lists
based on
a
common email address. I want to match email addresses between the
two
spreadsheets and bring the data from the second spreadsheet to the
matching
record (email address) on the first spreadsheet. I've read all
sorts of
online articles and read help, but still can't understand how to
get
vlookup
to work correctly.

Spreadsheet 1 is a master list with contact information for 400
people
including their email addresses in column R. Spreadsheet 2 is a
second
list
of 200 names with their email address in column E and the Response
in
column
F (column 6). I want to exact match records on email address and
copy the
Response in column F, spreadsheet 2 to the corresponding email
address row
in
the new column on spreadsheet 1. The lists have header rows.

I created a new column in Spreadsheet 1.
Then in Row 2 in that new column, I created:
vlookup(R2, (spreadsheet 2 table range to look for matching email
address),
(spreadsheet 2 column to grab the Response), False)

* Vlookup is located in cell W2
* R2 in spreadsheet 1 is the cell with the corresponding email
address
* spreadsheet 2 column to grab the Response: I tried using 6 for
the
column
F in spreadsheet two where Response is located. I also tried
highlighting
the entire column 6 in spreadsheet 2.
* False for exact match

I can't get vlookup to work properly. I hope that my explanation
is
clear.
I would greatly appreciate any help.

 

[Gord Dibben]

Perhaps the trailing space is a non-breaking space which TRIM or CLEAN won't
remove.

Try removing by Edit>Replace

What: Alt + 0160(on numpad)

With: leave blank

Replace all.


Gord Dibben MS Excel MVP

New information: On spreadsheet #2, there appears to be a trailing space at
the end of the email address and Excel is not matching as a result of that.
I copied all of the email addresses into spreadsheet #1 and then used the IF
function to determine if Excel was seeing the matches on the email address.
It was not seeing the matches because of that trailing space.

I looked closer at the formatting on spreadsheet #2 and the cells had
different alignment formatting. So I turned off wrap text and set alignment
the same on both spreadsheets.

I tried using the trim function to remove the trailing space, but it was not
successful. I also tried using Replace to get rid of the trailing space, but
the Replace function couldn't see the trailing space. However, when I click
my mouse on each cell, the trailing space is definitely there and when I hit
backspace to remove the trailing space, then Excel sees the email address as
being a match. So perhaps now my problem has shifted to trying to figure out
how to remove this trailing space before I can get vlookup to work properly.

Sorry, but I still can't figure it out. Is there something else that I'm
missing? Here's the formula that I tried based on what you suggested:

=VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)

- S2 is the cell in spreadsheet # 1 that contains the email address and I
want to find that exact email record in spreadsheet #2.

- [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260
is the table array referencing spreadsheet # 2 that contains the Reference
information that I want to bring into spreadsheet #1. I highlighted all the
data in the 2 column spreadsheet #2.

- Spreadsheet #2 is a two column spreadsheet that contains email address in
column #1 and the Reference information in column #2.

- I removed the hyperlinks from email addresses in both spreadsheets and
visually confirmed that there are no leading spaces.

dlee388,

in your example, "(spreadsheet 2 table range to look for matching email
address)" should be a range that is 2 columns wide by 200 + rows tall (make
sure your range addresses have "$" in them in case you need to copy the
formula down/over so it will refer to the same table range).

In order for the vlookup function to work, you need to give the column
number of the table range......not the column number of the physical column
on the sheet. In your case, this will be column 2 (second column of the
table range), not column 6 (physical column on the sheet).

HTH,

Conan Kelly




Can someone please help me with using vlookup to merge two lists based on
a
common email address. I want to match email addresses between the two
spreadsheets and bring the data from the second spreadsheet to the
matching
record (email address) on the first spreadsheet. I've read all sorts of
online articles and read help, but still can't understand how to get
vlookup
to work correctly.

Spreadsheet 1 is a master list with contact information for 400 people
including their email addresses in column R. Spreadsheet 2 is a second
list
of 200 names with their email address in column E and the Response in
column
F (column 6). I want to exact match records on email address and copy the
Response in column F, spreadsheet 2 to the corresponding email address row
in
the new column on spreadsheet 1. The lists have header rows.

I created a new column in Spreadsheet 1.
Then in Row 2 in that new column, I created:
vlookup(R2, (spreadsheet 2 table range to look for matching email
address),
(spreadsheet 2 column to grab the Response), False)

* Vlookup is located in cell W2
* R2 in spreadsheet 1 is the cell with the corresponding email address
* spreadsheet 2 column to grab the Response: I tried using 6 for the
column
F in spreadsheet two where Response is located. I also tried highlighting
the entire column 6 in spreadsheet 2.
* False for exact match

I can't get vlookup to work properly. I hope that my explanation is
clear.
I would greatly appreciate any help.