Searching for a range of values within a range of values

Posted on 2014-08-18
Medium Priority
Last Modified: 2014-08-18
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"

Question by:cyber-33
  • 2
  • 2
LVL 22

Expert Comment

by:Matt V
ID: 40268480
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.


Author Comment

ID: 40268666
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.
LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 40268739
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.


Author Closing Comment

ID: 40268974
Neat! Thank you, Glenn!
LVL 27

Expert Comment

by:Glenn Ray
ID: 40268976
You're welcome.  I'm gonna hang onto that function; it might be handy.


Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question