# Interesting VLookup quirk

#### normpam

##### Active Member
A1 B1 C1 D1
Norman 123 Elm st Norman =Vlookup(C1, \$A\$1:\$B\$10,2,false)

Just as a simple example. Problem is, it's not finding the value in C1 as 'Norman'. I've checked for spaces - not an issue. I did = Len() on both cells; they are the same. If I use = EXACT, they are different.

I noted that one of them was imported from another program and came in a a merged cell. Would this throw off the VLookup? And if so, is there a CHAR code I could look for and use SUBSTITUTE to fix the issue?

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
What cell in column A does "Norman" appear in?

When you do the LEN functions on both cells (C1 and its match in column A), what number exactly does it return?

Let's say that the match in cell A1. Then what does this formula return?
Excel Formula:
``=A1=C1``

What cell in column A does "Norman" appear in?

When you do the LEN functions on both cells (C1 and its match in column A), what number exactly does it return?

Let's say that the match in cell A1. Then what does this formula return?
Excel Formula:
``=A1=C1``
For one cell, the LEN is 11 in both. If I use =a1=c1 it returns FALSE

OK, you can obviously see that there is extra stuff in there, as "Norman" should only have a length of 6, not 11.

And the fact that =A1=C1 returns FALSE tells you that they do NOT match.

So there is a bunch of extra characters in them that need to be cleaned off.

If they are normal spaces, TRIM would work, i.e.
=TRIM(A1)

You can check to see if that will work by seeing if this returns 6:
=LEN(TRIM(A1))

If that does not, let's say what is on either end by returning the ASCII values of the left and right-most characters in A1, i.e.
=CODE(LEFT(A1,1))
=CODE(RIGHT(A1,1))

What values do those formulas return?

OK, you can obviously see that there is extra stuff in there, as "Norman" should only have a length of 6, not 11.

And the fact that =A1=C1 returns FALSE tells you that they do NOT match.

So there is a bunch of extra characters in them that need to be cleaned off.

If they are normal spaces, TRIM would work, i.e.
=TRIM(A1)

You can check to see if that will work by seeing if this returns 6:
=LEN(TRIM(A1))

If that does not, let's say what is on either end by returning the ASCII values of the left and right-most characters in A1, i.e.
=CODE(LEFT(A1,1))
=CODE(RIGHT(A1,1))

What values do those formulas return?
Sorry for the confusion.... I was just using the example of LEN being 11 based on a random entry.... Is there a way of checking to see if there might be a problem because of a merged cell code?

Sorry for the confusion.... I was just using the example of LEN being 11 based on a random entry.... Is there a way of checking to see if there might be a problem because of a merged cell code?
Do you have merged cells in your lookup table?
If so, please get rid of them! Merged cells cause nothing but trouble, and should be avoided if at all possible.
There are other alternatives that are much better.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Could you actually post a real sample of your data so we can see what you are actually working with?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Do you have merged cells in your lookup table?
If so, please get rid of them! Merged cells cause nothing but trouble, and should be avoided if at all possible.
There are other alternatives that are much better.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Could you actually post a real sample of your data so we can see what you are actually working with?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
There are no merged cells in the current data. It seems, however, that one of the files at one point had merged two cells with name and address info. All I really need to know at this point is whether or not there is a code similar to CHAR 10, that I could use to determine if the problem has to do with some kind of code for a merged cell....

Please identify an apparent match that is not working, and then apply all the tests I mentioned in post 4, and let me know the results.
It would also be helpful to see an image of your data, if not with XL2BB, then just a print image.

Let me see if I can simplify this. It's really pretty straightforward.
In a particular cell there is the entry K Buildings. There is another sheet that has the exact same entry, but a VLookup doesn't work. If I do a 'Find' for a space, Excel tells me that there is NO space in the cell that has the first entry of K Buildings. I've seen this before, and found that using a SUBSTITUTE formula to get rid of the CHAR 32 (space) character will work. However, when I use the SUBSTITUTE formula on this cell, it turns out that the 'space' between K and Buildings, is not only not a space, but is not the result of CHAR 32 either. What else could be lurking in the cell that makes it appear that there is a space in it?

Use this VBA code to find the ASCII code of every single character in your string.
Simply select the cell you want to analyze, and run the code.
VBA Code:
``````Sub GetASCIICodes()

Dim cd As String
Dim i As Long

'   Loop through each character of activecell and get ASCII code
For i = 1 To Len(ActiveCell)
cd = cd & "Character " & i & ": " & Mid(ActiveCell, i, 1) & " - " & Asc(Mid(ActiveCell, i, 1)) & vbCrLf
Next i

'   Return analysis
MsgBox cd

End Sub``````

Replies
10
Views
733
Replies
1
Views
111
Replies
1
Views
381
Replies
3
Views
372
Replies
8
Views
333

1,203,189
Messages
6,054,021
Members
444,696
Latest member
VASUCH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back