Results 1 to 15 of 18

20111102, 09:45 #1
 Join Date
 Dec 2001
 Posts
 41
 Thanks
 0
 Thanked 0 Times in 0 Posts
Casesensitve VLOOKUP not working with Lower Case
On Microsoft's Website, i found a way to do a Casesensitive 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 lowercase 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

20111102, 10:50 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,421
 Thanks
 368
 Thanked 1,454 Times in 1,323 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!May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20111102, 10:55 #3
 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

20111102, 11:10 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,421
 Thanks
 368
 Thanked 1,454 Times in 1,323 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

20111102, 11:21 #5
 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 crosseyed looking at this. Have I missed something obvious?
Sorry for being stupid!

20111102, 11:22 #6
 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 fileAndrew

20111102, 11:28 #7
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,421
 Thanks
 368
 Thanked 1,454 Times in 1,323 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)May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20111102, 11:34 #8
 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
blookup.jpgLast edited by AndrewKKWalker; 20111102 at 11:39. Reason: Added Picture
Andrew

20111102, 11:38 #9
 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)

20111102, 11:44 #10
 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!

20111102, 11:45 #11
 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.
Andrew

20111102, 11:48 #12
 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.

20111102, 11:58 #13
 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 E1Andrew

20111102, 12:29 #14
 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!!!!

20111102, 13:23 #15
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,421
 Thanks
 368
 Thanked 1,454 Times in 1,323 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.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs