0

I have two columns in Excel:

      Column A
Row 1 Apple
Row 2 Blueberry
Row 3 Strawberry

      Column B
Row 1 App
Row 2 Application
Row 3 Appendage

I would like to use Column B to see if any cells within it exist within the given cell in Column A. So far, I have used the VLOOKUP and MATCH functions and I can't seem to get either to work properly, but MATCH seems to be the one I should be using. I tried using wildcards on Column B and it returns a value error. Here is what I have:

=MATCH(A1,"*"&B:B&"*",0)

Your help is greatly appreciated!

scallahan.cybernet
  • 18
  • 6

1 Answers1

1

There is a natural VBA solution. In a standard code module place:

Function PartialMatch(v As Variant, R As Range) As Variant
    Dim i As Long
    For i = 1 To R.Cells.Count
        If v Like "*" & R.Cells(i).Value & "*" Then
            PartialMatch = i
            Exit Function
        End If
    Next i
    PartialMatch = CVErr(xlErrNA)
End Function

Then where you want it in a spreadsheet you can use the formula:

=PartialMatch(A1,B:B)

It will give the index of the first partial match, if any exists, or #N/A if it doesn't. Note that a blank cell counts as a partial match, so you might want to make sure that the range that you pass the function contains no blanks (so don't pass the whole column). That, or redefine what you mean by a partial match.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • I'm glad that it works for you. You might also want to look into Microsoft's Fuzzy LookUp Add-in: https://www.microsoft.com/en-us/download/details.aspx?id=15011 . I haven't used it, but people sometimes discuss it on Stack Overflow and it sounds like it might solve your problem more directly. – John Coleman Jun 15 '16 at 15:13
  • I actually downloaded and installed it, but it gave an error when attempting to run it the first time so I had to disable it. Thanks for the suggestion though and for the brilliant code! – scallahan.cybernet Jun 15 '16 at 15:44