Finding a single word match in a text string and assigning a code

[Morton Detwyler]

I deal with many manufactures providing our products. Each of the products
contain a different keyword. I have been able to isolate the keywords from
each of the product descriptions and assign a unique manufacturing code. But
here is the issue a can not solve.

In column "A" I have the list of product descriptions from each of the
manufacturers
In column "B" I need to perform the search listed below
In column "C" I have the list of keywords; i.e. Delta, Galaxy, etc.
In column "D" I have the associated manufacture code to the keywords

Because the list of product descriptions and keywords number in the hundreds
I can not hardcode all of the keywords into the formulas. Plus, the keywords
can be anywhere within the text string. In cell B2 I would like to search
cell A2 for the keyword from the range of C2:C200, and then assign the
associated manufacture code from the range of D2200. And, I would need to
copy this formula down column "B" so it can perform the same process against
the range of entries in A2:A500.

I've tried nested if, countif, vlookup, and array formulas, but can not get
them to work.

Any help and guidance would be very much appreciated.

 

[JP Ronse]

Hi Morton,

It looks as you have forgotten something to add to your request which make
it difficult for us to understand. Please provide some sample data and what
you want to become.

Wkr,

JP

 

[Ron Rosenfeld]

I deal with many manufactures providing our products. Each of the products
contain a different keyword. I have been able to isolate the keywords from
each of the product descriptions and assign a unique manufacturing code. But
here is the issue a can not solve.

In column "A" I have the list of product descriptions from each of the
manufacturers
In column "B" I need to perform the search listed below
In column "C" I have the list of keywords; i.e. Delta, Galaxy, etc.
In column "D" I have the associated manufacture code to the keywords

Because the list of product descriptions and keywords number in the hundreds
I can not hardcode all of the keywords into the formulas. Plus, the keywords
can be anywhere within the text string. In cell B2 I would like to search
cell A2 for the keyword from the range of C2:C200, and then assign the
associated manufacture code from the range of D2200. And, I would need to
copy this formula down column "B" so it can perform the same process against
the range of entries in A2:A500.

I've tried nested if, countif, vlookup, and array formulas, but can not get
them to work.

Any help and guidance would be very much appreciated.

There may be a simpler method, but the following appears to work:

I Name'd the range C2:C200 --> KeyWords
D2200 --> ProductCode

This formula must be **array-entered**:

A2:

=INDEX(ProductCode,MATCH(TRUE,ISNUMBER(MATCH("*"&KeyWords&"*",A2,0)),0),1)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

Then fill down to A500
--ron

 

[T. Valko]

Try this:

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2$200)

Note that if a cell contains more than one keyword the formula will "find"
the keyword that appears furthest down in the keyword list in C2:C200. For
example:

A2 = Super Duper Delta v1 Galaxy

Keyword list =

C2 = Galaxy
C3 = Delta

A2 contains both keywords Delta and Galaxy. In the keyword list Delta
appears furthest down the list so the formula will "find" Delta and return
the corresponding value from column D.

 

[Morton Detwyler]

Hi Ron,
Thank you so much for the solution. It worked perfectly!

 

[Morton Detwyler]

Hi Biff,
I was able to use Ron's solution. You've helped me in the past and would
like to try your suggestion, but could not get it to work. Should the LOOKUP
be a VLOOKUP? And the "1E100", should that be the range in column A of
A2:A500?

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2$200)

Thanks for your time.....

 

[T. Valko]

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2$200)

A2 is the cell that contains the text string
C2:C200 is your list of keywords
D2200 are the codes that correspond to the keywords

Let's assume A2 contains the text string Super Delta V1 Rocket

...........C..........D
2......Sun........AA1
3......Delta......BC1
4......Moon....X1C

=LOOKUP(1E100,SEARCH(C$2:C$4,A2),D$2$4)

Returns BC1

It found the keyword "Delta" in the string in cell A2.

 

[Morton Detwyler]

Thank you very much.....your solution worked. Although I am familiar with
the LOOKUP function, I have never seen "1E100" used before. But, I just read
your explanation for its use on eggheadcafe and it made perfect sense.
Thanks again!

 

[T. Valko]

Good deal! Thanks for the feedback!