vlookup Problem

M

marksuza

HI,

I am having a little problem with a vlookup formula and I was wondering
if anyone could help me. I copied and pasted two tables from the
internet to excel and I trying to combine information from both (they
have one column in common), I am using a vlookup formula but it is not
working, =VLOOKUP(A6,$N$4:$O$348,2). Now, if I choose a specific
record and copy and paste the name of that record (A6, for example) to
the corresponding record in column 'N', the formula works. I guess
excel is not identifying the names are the same. Can anybody help me
solve this? Thanks a lot.

Regards,

Mark
 
D

Dave O

Hi, Mark-
It sounds like the entries in the different files are visually
identical but not truly identical. For example, one file may contain
"John Doe" and the other "John Doe " (note the extra space at the end
of Doe). Or the entry may start out with an apostrophe, which happens
frequently with imported files.

Is this happening in your data?
 
M

marksuza

Yes Dave, I just checked and in one of them I have an extra space at th
end. Is there a formula to get this space out in all the rows. Thank
for the help once again.

Regards,

Marco
 
D

Dave Peterson

Maybe inserting a helper column of cells with formulas like:

=trim(a1)
and drag down.
Then copy|paste special|values over the original range and delete the helper
column.

=trim() will remove leading/trailing/repeated embedded spaces, too

If you want to cheat (this is usually a bad idea!):

=VLOOKUP(A6,$N$4:$O$348,2)
becomes
=VLOOKUP(trim(A6),$N$4:$O$348,2)

But it's always better to fix the data.
 
Top