vlookup

[mypig7]

sheet 1
- I have a first name in 1 box and a surname in another. I then combined them using '&' in a formula, with a space, and pasted values so I now have first name space surname
- I then tried to lookup that name in Sheet 2, and bring back a value next to it.


My issue
I get #n/a as the result.
In Sheet 1 the Vlookup looks at the new combined name, (as detailed above), if I retype the name over the top of it's self, the vlookup works.

Help!

 

[Finalfight40]

Strange i just tried and it works fine for me.

Maybe try trimming the values when combining them?

=Trim(A1) & " " & Trim(B1)

for example

 

[mypig7]

Thanks, but that doesn't solve it. I think its because the data was exported from another system.
If I type in the name manually (first name space surname) it works, instead of combining first name space and surname. It's just that I have thousands of names

 

[Joe4]

Take a look at a match that isn't working, and apply the LEN function to each list and see what it returns.
Does it show the length of the two being the same?
Sometimes if data comes from external sources, it has extra spaces or special characters.
In order for the VLOOKUP to work, they must match EXACTLY (it doesn't matter if they were built by formula or not).

So the key here is to first identify what is different. Then we can work on cleaning up the data so that it will work.

 

[jtakw]

Hi,

May be also try this:

Book1
	A	B	C
1	John	Doe	John Doe
Sheet81

Cell Formulas
Range	Formula
C1	=TRIM(CLEAN(A1&" "&B1))