Results 1 to 3 of 3

Thread: Vlookup and wildcard search problem

  1. #1

    Vlookup and wildcard search problem

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a vlookup, wild card search question. I have a table array called project table with values:


    and an input table:

    14824, 14892, 14571, 14457, 14762
    14052, 14151, 14278

    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?



  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    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 D550:


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

    Here's the 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
            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
        Application.EnableEvents = True
    End Function
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    You are awesome. Thanks for the help.

Posting Permissions

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