Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Case-sensitve VLOOKUP not working with Lower Case

    On Microsoft's Website, i found a way to do a Case-sensitive VLOOKUP, but it is only working/finding UPPER CASE. I need it to find Lower Case too. We're moving from Salesforce to CRM, and the Salesforce IDs are both Upper & Lower Case, eg,
    a0720000009he3Y might be Coca Cola
    a0720000009he3y might be Pepsi Cola

    I'm matching records, so I need it to find both Upper & Lower Case. At the moment, I get Coca Cola for the first one because it seems to end in an Upper Case Y, but the Lower Case record ending with a lower-case y is returning "No exact match" even though an exact match exists.

    Can anyone help, please?
    This is my current formula:
    =IF(EXACT(A1,VLOOKUP(A1,BranchLookup,1,FALSE))=TRU E,VLOOKUP(A1,BranchLookup,2,FALSE),"No exact match")

    Thanks in advance for any help.
    Brian

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brian,

    The solution provided by MS is not complete! If the table is extended to contain both Joe & joe it will still return "No exact match" if the first occurrence of Joe is not the case you are searching for. The lookup functions { V & H } are case insensitive and the solution MS provides will only work if only ONE case version exists in the lookup table!
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Dec 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks so much for the reply. Do you know if there is a workaround, ie, a Function I can use for what I need to achieve?
    Many thanks in advance.
    Brain

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brian,

    I found this and it works! Scroll down to the User Defined Function method as I think it is the easiest to use. Just past it into a Module in the workbook, then call it from your worksheet as follows: =CaseVLook(A1,BranchLookup,2)
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Lounger
    Join Date
    Dec 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again RG. I've copied the code into a VBA as follows:


    Function CaseVLook(compare_value, table_array As Range, _
    Optional col_index As Integer = 1)
    Dim c As Range
    Dim rngColumn1 As Range

    Application.Volatile

    Set rngColumn1 = BranchLookup.Columns(2)
    CaseVLook = "Not Found"

    'Loop first column
    For Each c In rngColumn1.Cells
    If c.Value = compare_value Then
    CaseVLook = c.Offset(0, col_index - 1).Value
    Exit For
    End If
    Next c
    End Function

    In a Cell, I've entered the following:
    =CaseVLook(E41,BranchLookup,2)

    But I've just gotten a #NAME? value returned.
    I'm cross-eyed looking at this. Have I missed something obvious?

    Sorry for being stupid!

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Match and Index variation with exact

    You can try it with Exact and using Match and Index


    caselookup.jpg



    I have attached an example file
    Attached Files Attached Files
    Andrew

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brian,

    Where did you put the code. It needs to go into a Module. If you copied it into the existing Sheet or Workbook items it won't work. Use Insert->Module in the VBA Editor screen to create a module. (see example)
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The module is not quite right as far as I can see.

    It ought to be

    Code:
    Function CaseVLook(compare_value, table_array As Range, _
    Optional col_index As Integer = 1)
    Dim c As Range
    Dim rngColumn1 As Range
    
    
    Application.Volatile
    
    'This line was wrong
    Set rngColumn1 = table_array.Columns(1)
    CaseVLook = "Not Found"
    
    'Loop first column
    For Each c In rngColumn1.Cells
        If c.Value = compare_value Then
            CaseVLook = c.Offset(0, col_index).Value
            Exit For
        End If
    Next c
    
    End Function
    Mind you that is for my data below .....

    blookup.jpg
    Last edited by AndrewKKWalker; 2011-11-02 at 12:39. Reason: Added Picture
    Andrew

  9. #9
    Lounger
    Join Date
    Dec 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank RG

    Got it, but all I get is "Not Found".

    This is the Code I've entered now:

    Option Explicit
    Function CaseVLook(compare_value, BranchLookup As Range, _
    Optional col_index As Integer = 1)
    Dim c As Range
    Dim rngColumn1 As Range

    Application.Volatile

    Set rngColumn1 = BranchLookup.Columns(2)
    CaseVLook = "Not Found"

    'Loop first column
    For Each c In rngColumn1.Cells
    If c.Value = compare_value Then
    CaseVLook = c.Offset(0, col_index - 1).Value
    Exit For
    End If
    Next c
    End Function

    With this in the formula: =CaseVLook(E41,BranchLookup,2)

  10. #10
    Lounger
    Join Date
    Dec 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew, thanks also. I tried that, but I just get an #N/A returned.
    This is the formula I used:
    =INDEX(AcType!$B$1:$B$4178,MATCH(TRUE,EXACT(E41,Ac Type!$A$1:$A$4178),0))

    My Array is on a Sheet called AcType.
    The Values to lookup are A1 -> A4178.
    The Values to be returned are B1 -> B4178

    The Column its looking up to compare against this array is E1 -> E4199 on a different sheet (eg, Sheet 1).
    So, in Sheet 1, I've put the formula in C1. It looks up E1, and then has to find a match in the Array on the Sheet called AcType.

    As stated, though, I just get an #N/A.

    Can you see what I've done wrong in the formula?

    Thanks again for your help too!

  11. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have a look at the updated attached file.
    Attached Files Attached Files
    Andrew

  12. #12
    Lounger
    Join Date
    Dec 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank again Andrew for your help with the VB. Although I no longer get an error, I just get a 1 and not a value. the Value should be, eg, HQ, Branch Office, etc (ie, text, not numbers).
    Hoping one of these will eventually work! I've wasted all day trying to get this Lookup working.

  13. #13
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Difficult to know without the data.
    The formula you have is looking for the value in E41, so IF you want to look for the value in E1 the formula in C1 ought to be

    =INDEX(AcType!$B$1:$B$4178,MATCH(TRUE,EXACT(E1,Ac Type!$A$1:$A$4178),0)) (Entered with SHIFT CTRL and ENTER)

    This should look for a case sensitive match with the value in E1
    Looking for a match in A1:A4178 on the AcType sheet, and bringing back the corresponding value from B1:B4178 on the AcType sheet.

    #N/A implies it cannot find a match.

    It can be as simple as an extra space in the data in E1
    Andrew

  14. #14
    Lounger
    Join Date
    Dec 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Andrew. Think it's working! Thank God for you! I've wasted all day and finally have the Function required.
    Thank you both so much for your help!!!!

  15. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brian,

    Here's the test worksheet I used with the UDF function as I copied it from the link. It works fine for me.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •