Case Sensitive Vlookup VBA/Formula

Sandeep Warrier

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,680
Hey All,

Am trying to do a case sensitive vlookup. I think it'll be too messy via formula and trying to write a UDF for the same.
The UDF below works fine. Could anyone suggest possible enhancements or other methods to do the same??

Code: 
Function CaseSensitiveLookup(LookupValue As Variant, LookupRange As Range, Optional ColIndex As Integer) As Variant

    Application.Volatile
    
    Dim rCell As Range
    
    If ColIndex < 0 Then
    
        CaseSensitiveLookup = "Column Index Should be greater than 0"
        Exit Function
    
    End If
    
    With LookupRange
    
        Set rCell = .Find(What:=LookupValue, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
                    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
                    , SearchFormat:=False)
                
        If Not rCell Is Nothing Then
        
            If ColIndex > 1 Then
            
                CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value
            
            Else
            
                CaseSensitiveLookup = rCell.Value
            
            End If
        
        Else
        
            CaseSensitiveLookup = "Value Not Found"
        
        End If
                
    End With

End Function
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hi Sandeep

I believe you have an error here:

Code: 
Set rCell = .Find(What:=LookupValue, [COLOR=red]After:=.Cells(1, 1)[/COLOR], ...

This means that you are starting the search in the second cell and not in the first cell like the Vlookup(). I think it should be:

Code: 
       Set rCell = .Find(What:=LookupValue, After:=[COLOR=royalblue].Cells(.Rows.Count, 1)[/COLOR], ...

Suggestions:

- I'd replace:

Code: 
            If ColIndex > 1 Then
            
                CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value
            
            Else
            
                CaseSensitiveLookup = rCell.Value
            
            End If

with

Code: 
        CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value

- I believe the test to the lower limit of ColIndex should be "<=" and not just "<". You could also add the upper limit:

Code: 
    If (ColIndex <= 0) Or (ColIndex > LookupRange.Columns.Count) Then

Remark: the code reflects the case when the fourth parameter in Vlookup is False
 
Upvote 0
Hi PGC..

Thanks a lot for your comments..

pgc01 said:
Code: 
Set rCell = .Find(What:=LookupValue, [COLOR=red]After:=.Cells(1, 1)[/COLOR], ...
This means that you are starting the search in the second cell and not in the first cell like the Vlookup(). I think it should be:

Code: 
       Set rCell = .Find(What:=LookupValue, After:=[COLOR=royalblue].Cells(.Rows.Count, 1)[/COLOR], ...
Click to expand...

My code would begin from the second cell cuz of After:=.Cells(1,1) which means it wud begin after the 1st Cell. While with your suggestion it would be .Cells(65536,1) and the cell after that would I guess loop back to the 1st Cell. Is my understanding correct here?

However, I do get correct results with .Cells(1,1) even if the lookup value is the 1st value in the lookup range. I do believe that, if my understanding is correct, your suggestion would improve the running time since it'll begin from the 1st cell as opposed to mine where it'll start from cell 2 and go all the way down and then loop back to cell 1.

Suggestions:

- I'd replace:

Code: 
            If ColIndex > 1 Then
            
                CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value
            
            Else
            
                CaseSensitiveLookup = rCell.Value
            
            End If
with

Code: 
        CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value
- I believe the test to the lower limit of ColIndex should be "<=" and not just "<". You could also add the upper limit:

Code: 
    If (ColIndex <= 0) Or (ColIndex > LookupRange.Columns.Count) Then
Click to expand...
Since I have defined the ColIndex as Integer and to be optional, wouldnt it mean that if its left blank, it'll take a default value of 0? I've seen this in a few of my other codes where a variable defined as integer takes the value 0 if left blank. If so, ColIndex <= 0 would ensure that it always fails the test!!! Including the test for the upper limit was a very good idea. Thanks for suggesting it.

I was wondering if I should remove the test for lower limit alltogether, just leaving the upper limit test so that it would function more like an Index(Match()) function.

Remark: the code reflects the case when the fourth parameter in Vlookup is False
Click to expand...
Yes I know. This is mainly cuz I was looking for exact matching. I was wondering how the logic would proceed if I was to look at including the TRUE part.

Once again, thanks a lot for your feedback and suggestions. Any more feedbacks would be greatly appreciated.
 
Last edited:
Upvote 0
Hi again Sandeep

sandeep.warrier said:
My code would begin from the second cell cuz of After:=.Cells(1,1) which means it wud begin after the 1st Cell. While with your suggestion it would be .Cells(65536,1) and the cell after that would I guess loop back to the 1st Cell. Is my understanding correct here?
Click to expand...

Yes, that's the idea. It's not good practice, however, to use hardcoded values for these kind of parameters because they will depend on the version of excel and of the lookup range. The 65536 will work if, for example, you are working in excel 2000 and the lookup range is a whole column. It would not work for excel 2007 with a whole column, because it has more rows or if you select a smaller range. ".Cells(.Rows.Counts, 1)" will work with any version and any lookup range.

sandeep.warrier said:
However, I do get correct results with .Cells(1,1) even if the lookup value is the 1st value in the lookup range. I do believe that, if my understanding is correct, your suggestion would improve the running time since it'll begin from the 1st cell as opposed to mine where it'll start from cell 2 and go all the way down and then loop back to cell 1.
Click to expand...

No. That was not my point. The question for me is that Vlookup returns the values corresponding to the first value found. With your code it will return a wrong value when the value is found in the first row, unless it happens to be unique. For example:

Try:

=CaseSensitiveLookup("Hi",B3:C6,2)


<TABLE style="BORDER-BOTTOM-COLOR: #cccccc; BORDER-RIGHT-WIDTH: 2px; BORDER-TOP-COLOR: #cccccc; BORDER-COLLAPSE: collapse; FONT-FAMILY: Arial,Arial; BACKGROUND: #fff; BORDER-TOP-WIDTH: 2px; BORDER-BOTTOM-WIDTH: 2px; BORDER-RIGHT-COLOR: #cccccc; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #cccccc; BORDER-LEFT-WIDTH: 2px" border=1 cellPadding=1><TBODY><TR><TH style="BORDER-BOTTOM-COLOR: #888888; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px"></TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>A</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>B</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>C</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>D</TH></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">Hi</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">hi</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">Hi</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">hI</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">7</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="PADDING-LEFT: 1em; BACKGROUND: #9cf" colSpan=5>[Book1]Sheet1</TD></TR></TBODY></TABLE>

You'll see that with your code you get the value 3 when you should get the value 1. If you modify it as I posted the search will begin in the first cell in the range and you'll get 1 as expected.


sandeep.warrier said:
Since I have defined the ColIndex as Integer and to be optional, wouldnt it mean that if its left blank, it'll take a default value of 0? I've seen this in a few of my other codes where a variable defined as integer takes the value 0 if left blank. If so, ColIndex <= 0 would ensure that it always fails the test!!! Including the test for the upper limit was a very good idea. Thanks for suggesting it.

I was wondering if I should remove the test for lower limit alltogether, just leaving the upper limit test so that it would function more like an Index(Match()) function.
Click to expand...

This is your function and you should do it the way you see fit. In my opinion the ColumnIndex value should not be 0. If you try Vlookup() in the worksheet with the 3rd parameter set to 0 you'll get a #VALUE! error. That would make your code no longer be compatible with the worksheet function.

If, however, you really want the code to return the value in the first column I would prefer to initialise the parameter in the function declaration:

Code: 
Function CaseSensitiveLookup(LookupValue As Variant, LookupRange As Range, Optional ColIndex As Integer = 1) As Variant

This way you can still just use just:

Code: 
CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value

and the udf is still similar to the Vlookup() since you are allowed to use the Vlookup in the worksheet with the third parameter set to 1 and it will return the value in the first column like your code will.

I don't understand why you would want to do it. It would simply return the value itself, if it exists in the first column of the input range or a "Value not found".

I still think you should keep the validation of the lower limit. You declared the parameter as Integer and so the syntax allows you to call the udf with the third parameter negative, which is not allowed in a Vlookup().
 
Last edited:
Upvote 0
Hey PGC,

Thanks a lot for your explanations. I have modified the code per your suggestions.

The main purpose of this code was to lookup exact matches. However, if I wanted to modify this to include the TRUE part (4th part of vlookup), what modifications would I be looking at?

Initially I thought of using another .Find with LookAt:=xlPart instead of xlWhole. But this would mainly take care of text and that too only if the entire lookup value is present in the range and make a mess of numbers.

Thanks a lot once more for all your help.

Code: 
Function CaseSensitiveLookup(LookupValue As Variant, LookupRange As Range, Optional ColIndex As Integer = 1) As Variant

    Application.Volatile
    
    Dim rCell As Range
    
    If (ColIndex <= 0) Or (ColIndex > LookupRange.Columns.Count) Then
    
        CaseSensitiveLookup = "Please Enter a valid Column Index"
        Exit Function
    
    End If
    
    With LookupRange
    
        Set rCell = .Find(What:=LookupValue, After:=.Cells(.Rows.Count, 1), LookIn:=xlFormulas, LookAt:= _
                    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
                    , SearchFormat:=False)
                
        If Not rCell Is Nothing Then
        
            CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value
                    
        Else
        
            CaseSensitiveLookup = "Value Not Found"
        
        End If
                
    End With

End Function
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 
Upvote 0
Hi Again

1 - Suggestion:The Vlookup() function demands that the lookup range be sorted in ascending order or you cannot trust the result. Since you want your udf to be similar to the Vlookup() you could used the same criterium.

I think that in this case you have to loop through the values in the the first column of the lookup range.

For example (not much tested): Using a boolean 4th parameter, bLTE (Less Than or Equal)

Code: 
Function CaseSensitiveLookup(LookupValue As Variant, LookupRange As Range, Optional ColIndex As Integer = 1, Optional bLTE As Boolean = True) As Variant
Dim rCell As Range
 
    If (ColIndex <= 0) Or (ColIndex > LookupRange.Columns.Count) Then
 
        CaseSensitiveLookup = "Please Enter a valid Column Index"
        Exit Function
    End If
 
    CaseSensitiveLookup = "Value Not Found"
 
    With LookupRange
 
        If bLTE Then
            For Each rCell In .Columns(1).Cells
                If rCell.Value <= LookupValue Then CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value
            Next rCell
 
        Else
            Set rCell = .Find(What:=LookupValue, After:=.Cells(.Rows.Count, 1), LookIn:=xlFormulas, LookAt:= _
                        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
                        , SearchFormat:=False)
            If Not rCell Is Nothing Then CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value
        End If
    End With
End Function

You can improve it. For example:
- if the 4th parameter is True, instead of returning a possibly incorrect result when the range is not sorted in ascending order, you can detect it and return a message like: "Please sort the lookup range".
- check that the Lookup value and the values in the first column of the lookup range have the same type

Remark: I assumed that you did not alter the default case sensitive comparison of vba.


2 - I see you had the Application.Volatile in your code. You should avoid it since it's very inefficient. In this case you don't need it so just remove it.

HTH
 
Upvote 0
Hello,

Your modifications with the code seem to work fine.

I did try to insert a check for sorting and a check for datatypes... however they dont seem to be working.

Code: 
            For Each rCell In .Columns(1).Cells
                If rCell.Value > rCell.Offset(1, 0).Value Then CaseSensitiveLookup = "Please sort Data in an Ascending order"
                If TypeName(rCell.Value) <> TypeName(LookupValue) Then CaseSensitiveLookup = "Please ensure data type in Lookup Column is constant"
            Next rCell

The codes work in a normal sub, but when I try to include them within the function, they dont return any value.

The problem with the check for sorting is that when it reaches the last cell, it'll check the value with the next cell, which might be empty, resulting in a 0 and it'll always give the message for sort (as seen in a normal Sub).

Maybe I'll be able to think better after a good night's sleep :)

Thanks for all your help.
 
Upvote 0
Hi

I'm glad it's working ok.

I remembered that, after you solve the new checkings, one thing interesting would be to add another parameter with the order of the match for the case of duplicates. In the table I posted the first match of "Hi" is 1 and the second is 3. This would make the udf, however, no longer just a case sensitive VLookup().

Cheers
 
Upvote 0
Hello,

For some reason I couldnt get the "Check Sort" and "Check Data Type" to work.

However, I did include the fifth parameter of Instance. Changed the code a bit cuz I didnt know how to use .Find to get more than one instance.

Code: 
Function CaseSensitiveLookup(LookupValue As Variant, LookupRange As Range, Optional ColIndex As Integer = 1, Optional bLTE As Boolean = True, Optional Instance As Integer = 1) As Variant

    Dim rCell As Range
    Dim i As Integer
    Dim WF As WorksheetFunction
    
    Set WF = Application.WorksheetFunction
    i = 0
    
    If (ColIndex <= 0) Or (ColIndex > LookupRange.Columns.Count) Then
    
        CaseSensitiveLookup = "Please Enter a valid Column Index"
        Exit Function
    
    End If
    
    
    CaseSensitiveLookup = "Value Not Found"
    
    With LookupRange
    
        If Instance > WF.CountIf(.Columns(1), LookupValue) Then CaseSensitiveLookup = "Instance is larger than count of lookup value"
    
        If bLTE Then
        
            For Each rCell In .Columns(1).Cells
            
                If rCell.Value <= LookupValue Then
                
                    i = i + 1
                    
                    If i = Instance Then
                    
                        CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value
                        Exit Function
                    
                    End If
                
                End If
            
            Next rCell
        
        Else
        
            For Each rCell In .Columns(1).Cells
            
                If rCell.Value = LookupValue Then
                
                    i = i + 1
                    
                    If i = Instance Then
                    
                        CaseSensitiveLookup = rCell.Offset(0, ColIndex - 1).Value
                        Exit Function
                    
                    End If
                
                End If
            
            Next rCell
                
        End If
    
    End With

End Function
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 
Upvote 0
That's great, that parameter may come in handy.

Changed the code a bit cuz I didnt know how to use .Find to get more than one instance.
Click to expand...

That's not a bad thing since the Range.Find() method did not work in some previous versions of excel (for ex. in excel 2000), only in the last versions. This way the udf is more version independent.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
261
Micron
M
C
  • Solved
User Select PDF import to specific Excel sheet
2 3 4
Replies
31
Views
1K
John_w
J
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
372
Trixterz
T
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
422
mumps
mumps
A
  • Question Question
VBA Highlight cell if contains specific text
Replies
4
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,802
Messages
6,181,054
Members
453,014
Latest member
Chris258

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top