Searching for a range of values within a range of values

I have an excel sheet Sheet1 with a set of text data values (20-50 words) in column A.
An excel sheet Sheet2 stores a set of keywords in Col. A.

I need a macro that loops through each value in Sheet1/Col.1, and searches for each values listed in Sheet2/Col.A. If it finds one or more values, the one or more values should be written in Sheet1/Col.B, If the macro does not find any values, the corresponding Cell. in Sheet1/Col. B should display not found.



ColA/Sheet 1
A1 = "This is a test string"
A2 = "this is another value"


ColA/Sheet 1
A1 = "This is a test string", B1 = "test, string"
A2 = "this is another value", B2 = "NOT FOUND"

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Matt VCommented:
You should be able to do that with VLOOKUP

Here is a good walk-through that should help you.  Using VLOOKUP, SMALL and INDEX to return multiple values on a single lookup.
cyber-33Author Commented:
The example you provided shows vlookup to search for a single value in an array of records. In the problem I described above, I have a dynamic array of values that I need to search for. The idea is to be able to type keywords in column A of the sheet2 and have column B in sheet1 identify any existing keywords in the corresponding column a value. So, I don't see how vlookup can do it.
Glenn RayExcel VBA DeveloperCommented:
The following user-defined function (UDF) will produce the results you want:
Function Find_Matches(Test_Cell As Range, Lookup_array As Range) As String
    Dim cl As Object
    For Each cl In Lookup_array
        If InStr(1, Test_Cell, cl.Value, vbTextCompare) > 0 Then
            Find_Matches = Find_Matches & cl.Value & ", "
        End If
    Next cl
    If Find_Matches <> "" Then
        Find_Matches = Left(Find_Matches, Len(Find_Matches) - 2)
        Find_Matches = "NOT FOUND"
    End If
End Function

Open in new window

You just insert this into a module in your workbook and then call it like a regular Excel function like so:

I've attached an example file for you to try and test.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyber-33Author Commented:
Neat! Thank you, Glenn!
Glenn RayExcel VBA DeveloperCommented:
You're welcome.  I'm gonna hang onto that function; it might be handy.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.