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:

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    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:

    =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
    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: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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
  •