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 M is for Data Monkey, 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