Vlookup and wildcard search problem

W

WSmith

New member
Joined
Nov 1, 2012
Messages
2
Reaction score
0
Points
0
I have a vlookup, wild card search question. I have a table array called project table with values:

14824
14002
14151
14447
14360
14240
14571

and an input table:

14962
14824, 14892, 14571, 14457, 14762
14471
14571
14052, 14151, 14278
14824
14002

And I used the following vlookup code to tell me if any of the values in the input table is in the project table.

=VLOOKUP("*"&A2&"*",Project Table!A2:A8, 1, FALSE)

It gives me the right answer if the cell only has one value. if it has multiple values it gives me a 'False'.

I just want it to give me a true or false indication. To be clearer, I would like the input table to give me a true indication as one of the input projects value is in the project table.

Can you please help me?

Thanks

Will
 
Hi Will,

Sorry for the delay in replying here, I got caught by a couple of busy days.

I'm sure that one of my colleagues can come up with a miraculous array formula solution for this, but I would tend to reach for VBA in this case. What I've done here is build a custom function for you called "CustomLookup". You'd use it like this:

=CustomLookup(range to look for, range with data table)

So if the data you're looking for is in A5, and your project table is in D5:D50:

=CustomLookup(A5,$D$5:$D$50)

The code needs to go into a standard VBA code module, and you can find steps to do that here.

Here's the code:

Code: 
Public Function CustomLookup(rngLookFor As Range, rngLookIn As Range) As Boolean
    Dim ary() As String
    Dim lIndex As Long
    Dim lMatch As Long
    
    Application.EnableEvents = False
    If IsNumeric(rngLookFor) Then
        On Error Resume Next
        lMatch = Application.WorksheetFunction.Match(rngLookFor, rngLookIn, False)
        If Err.Number = 0 Then
            customlookup = True
            GoTo ExitPoint
        End If
    Else
        ary() = Split(rngLookFor, ",")
        For lIndex = 0 To UBound(ary)
            On Error Resume Next
            lMatch = Application.WorksheetFunction.Match(CLng(Trim(ary(lIndex))), rngLookIn, False)
            If Err.Number = 0 Then
                customlookup = True
                GoTo ExitPoint
            End If
            On Error GoTo 0
        Next lIndex
    End If
ExitPoint:
    Application.EnableEvents = True
End Function
 
You must log in or register to reply here.
Back
Top