Link to home
Start Free TrialLog in
Avatar of munch007

asked on

Excel Search for List of Words in a cell

I have a list of words that I want to search and return a value of "1" if found in the corresponding cell.
Here is an example
List of words:

cell Description  
b2  round filter  
b3 bolt
b4 caplug
b5 cap 1.25in

I would expect it to return a 1 for cell b2 and a 1 for cell b5.

See attached file.
Avatar of Matt Coughlin
Matt Coughlin
Flag of United States of America image

Which version of Excel?
Avatar of munch007


Excel 2003
Do you want to be able to add or subtract from your list of words without changing your formula?
want to be able to add/remove words from the list without changing formula.  It would be ok to change the range that the list of words is in.
I am only able to do this with a macro.

Also in your data you have an item with a description of "capscrew" this will always get marked "1" because it contains the word "cap" as listed in your list of words.

Maybe someone else can do this with a formula, but I cannot seem to figure it out.

here is the macro:

Sub Macro1()
' Macro1 Macro
n = Range("b65536").End(xlUp).Row
o = Range("k65536").End(xlUp).Row

For i = 2 To n
    r = Range("b" & i).Value
For j = 2 To o
    s = Range("K" & j).Value
    If InStr(UCase(r), UCase(s)) >= 1 Then
    Range("g" & i).Value = 1
    GoTo 10
    End If
Range("g" & i).Value = 0
End Sub

Open in new window

I have this array formula, entered with Ctrl+Shift+Enter, but it currently picks up capscrew as a match


Nice work nutsch!
Avatar of nutsch
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Still as an array formula (validate with Ctrl+Shift+Enter instead of plain Enter)
thanks, it works