asked on

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.

Example:

INPUT:

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

ColA/Sheet2
test
string
macro

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

THANK YOU, EXPERTS!

Matt V

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.

http://office.microsoft.com/en-ca/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx#_Toc273640147

cyber-33

ASKER

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.

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyber-33

ASKER

Neat! Thank you, Glenn!

Glenn Ray

You're welcome. I'm gonna hang onto that function; it might be handy.

-Glenn