Interesting VLookup quirk

[normpam]

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?

 

[Joe4]

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?

=A1=C1

 

[normpam]

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?

=A1=C1

For one cell, the LEN is 11 in both. If I use =a1=c1 it returns FALSE

 

[Joe4]

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?

 

[normpam]

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?

 

[Joe4]

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.

 

[normpam]

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....

 

[Joe4]

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.

 

[normpam]

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?

 

[Joe4]

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.

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