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

Try removing by EditReplace

What: Alt + 0160(on numpad)

With: leave blank

Replace all.


Gord Dibben MS Excel MVP

On Mon, 3 Dec 2007 15:40:02 -0800, dlee388
wrote:

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.

"dlee388" wrote:

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

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" wrote in message
...
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.