How can I use wildcards with MyVlookup function?

Question

When calling the below function in Excel by using =MyVlookup(B1,Sheet1!A:A,1) I get what I want.

Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant

Dim cl As Range
For Each cl In c.Columns(1).Cells
If UCase(Lval) = UCase(cl) Then
MyVlookup = cl.Offset(, oset - 1)
Exit Function
End If
Next
End Function

But I'd like to use wildcards around B1, e.g. "*"&B1&"*". When I do that I get an error (#ARG!).
I tried using double quotation marks, but it didn't work, either. What should I do to overcome this?

B1 is passed to a parameter which you've defined in your UDF as Range. If you want to pass a string instead then you need to update the parameter type to As String If you need wildcard capability you'll also need to use Like instead of = — Tim Williams
Thanks for the reply. As this is not my code, and I'm not sure what should be changed to make it work, could you make a suggestion? — Maple

Santosh Santosh · Accepted Answer · 2013-12-09T17:53:36

Try below code

Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant

    Dim cl As Range
    For Each cl In c.Columns(1).Cells
        If InStr(1, UCase(cl), UCase(Lval), vbTextCompare) > 0 Then
            MyVlookup = cl.Offset(, oset - 1)
            Exit Function
        End If
    Next
End Function

String beginning with

Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant

    Dim cl As Range
    For Each cl In c.Columns(1).Cells
        If cl Like Lval & "*" Then
            MyVlookup = cl.Offset(, oset - 1)
            Exit Function
        End If
    Next
End Function

string ending with

Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant

    Dim cl As Range
    For Each cl In c.Columns(1).Cells
        If cl Like "*" & Lval Then
            MyVlookup = cl.Offset(, oset - 1)
            Exit Function
        End If
    Next
End Function

string which contains

Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant

    Dim cl As Range
    For Each cl In c.Columns(1).Cells
        If cl Like "*" & Lval & "*" Then
            MyVlookup = cl.Offset(, oset - 1)
            Exit Function
        End If
    Next
End Function

How can I use wildcards with MyVlookup function?

2 Answers